combining two columns into one in Framework Manager

[Cognosnewbie26]

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

[BigChris]

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.

[Cognosnewbie26]

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?

[BigChris]

Difficult to answer without knowing how your levels are defined. If they're a separate field, then you might want something like

Code Select Expand


case
  when [YourField] = 'g' then 'Level 3'
  else [Level Field]
end


[Cognosnewbie26]

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

[MFGF]

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.

[Cognosnewbie26]

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.