Hi,
How do I combine results of two columns into one column in the
Query subject. For e.g
Col1
A
B
C
Col2
Q
R
S
Combined column
A
B
C
Q
R
S
I am a newbie so please accept apologies if this seems really simple
Hi - the way that I would do that is to create two queries. The first query would bring back column1, and the second column2. Then you just need to union the two queries together.
Thanks that is a good suggestion but now I am realizing I have a different problem.
I am trying to create a custom hierarchy. Currently the hierarchy looks like this:
Level 1
Level 2
Level 3
a
b
c
Level 4
e
f
g
I have to move 'g' to level 3. So it should look like:
Level 3
a
b
c
g
I tried to include 'case when' logic to check at level 3 if the value at level 4 is g then get the data from level 4
That is not working. Any other suggestions?
Difficult to answer without knowing how your levels are defined. If they're a separate field, then you might want something like
case
when [YourField] = 'g' then 'Level 3'
else [Level Field]
end
Thanks I was able to figure it out. I created a query subject with custom sql using 'union all' then created a regular dimension including the custom levels from the sql query
Quote from: Cognosnewbie26 on 31 Jul 2020 01:19:22 PM
Thanks I was able to figure it out. I created a query subject with custom sql using 'union all' then created a regular dimension including the custom levels from the sql query
An alternative to hard-coding SQL like this (which isn't a best-practice) is to create a Query Set from two existing query subjects. You will end up with the same result, but the former will result in metadata callbacks, whereas the latter will not (and so will potentially be more efficient).
Cheers!
MF.
Quote from: MFGF on 03 Aug 2020 07:09:02 AM
An alternative to hard-coding SQL like this (which isn't a best-practice) is to create a Query Set from two existing query subjects. You will end up with the same result, but the former will result in metadata callbacks, whereas the latter will not (and so will potentially be more efficient).
Cheers!
MF.
Thanks for the suggestion. Since I am new I am definitely interested in knowing the best practices. However in this case I think that I did need a custom sql because the custom hierarchy needed some conditional statements.