Author Topic: combining two columns into one in Framework Manager  (Read 418 times)

Offline Cognosnewbie26

  • Full Member
  • ***
  • Join Date: Jul 2020
  • Posts: 6
  • Forum Citizenship: +0/-0
combining two columns into one in Framework Manager
« on: 29 Jul 2020 09:33:24 pm »
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

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,213
  • Forum Citizenship: +89/-0
Re: combining two columns into one in Framework Manager
« Reply #1 on: 30 Jul 2020 01:38:49 am »
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.

Offline Cognosnewbie26

  • Full Member
  • ***
  • Join Date: Jul 2020
  • Posts: 6
  • Forum Citizenship: +0/-0
Re: combining two columns into one in Framework Manager
« Reply #2 on: 30 Jul 2020 11:49:56 am »
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?
« Last Edit: 30 Jul 2020 11:51:59 am by Cognosnewbie26 »

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,213
  • Forum Citizenship: +89/-0
Re: combining two columns into one in Framework Manager
« Reply #3 on: 31 Jul 2020 12:40:22 am »
Difficult to answer without knowing how your levels are defined. If they're a separate field, then you might want something like
Code: [Select]
case
  when [YourField] = 'g' then 'Level 3'
  else [Level Field]
end

Offline Cognosnewbie26

  • Full Member
  • ***
  • Join Date: Jul 2020
  • Posts: 6
  • Forum Citizenship: +0/-0
Re: combining two columns into one in Framework Manager
« Reply #4 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

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,038
  • Forum Citizenship: +655/-10
  • Cognos Software Muppet
Re: combining two columns into one in Framework Manager
« Reply #5 on: 03 Aug 2020 07:09:02 am »
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.
Meep!

Offline Cognosnewbie26

  • Full Member
  • ***
  • Join Date: Jul 2020
  • Posts: 6
  • Forum Citizenship: +0/-0
Re: combining two columns into one in Framework Manager
« Reply #6 on: 07 Aug 2020 04:35:27 pm »
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.

 



       
Twittear