Combine three fields into one filed

[NavinReddy]

Hello All,

I have a requirement to show three fields in one dropdown section.

1. Org Level1
2. Org Level2
3. Org Level3

Now I need to show these fields under one drop down

LOB

Org Level1

Org Level2

Org Level3

Please help me

Thanks in Advance

Niranjan Pawar

[jobsonkjoseph]

Hi Niranjan,

I suppose by using Pivot table you can convert rows into columns and columns into rows.

Have you tried this instead.

[NavinReddy]

i have tried no use this wont fulfill my requirement, script side we have to do some thing to merge 3 fields to one filed

[Jesh19]

Hi Niranjan,

Try using ValueList.

In a straight table, under dimensions -> calculated dimension.

=ValueList('Org level 1','Org Level 2','Org Level 3')

Then write expressions as below.

Q1'18 - Pick(Match(ValueList('Org level 1','Org Level 2','Org Level 3'),'Org level 1','Org Level 2','Org Level 3'),Sum(1),Sum(2), Sum(3))

Q2'18 - Pick(Match(ValueList('Org level 1','Org Level 2','Org Level 3'),'Org level 1','Org Level 2','Org Level 3'),Sum(1),Sum(2), Sum(3))

Q3'18 - Pick(Match(ValueList('Org level 1','Org Level 2','Org Level 3'),'Org level 1','Org Level 2','Org Level 3'),Sum(1),Sum(2), Sum(3))

Change your expressions accordingly instead of Sum(1), 2 and 3...

Thanks,
Jesh

[chrismarlow]

Niranjan,

That tells me what you want (possibly), but not what you have got. I am a bit confused as I thought Org Level1 etc were the names of fields, that you then wanted to show the values for in the drop down.

So from experience organisations often have hierarchical structure, take a bank, Org Level1=The whole bank, Org level2 maybe you split into business divisions and infrastructure, Org Level3 the different divisions & the different parts of infrastructure, so there is a hierarchy;

Org Level 1	Bank
Org Level 2	Business Division		Infrastructure
Org Level 3	Commercial Banking	Retail Banking	HR	Technology	Finance

So from that I thought you wanted your equivalents of 'Bank', 'Business Division', 'Infrastructure', 'Commercial Banking', etc to be in one drop down, but by definition in a hierarchy your totals at each level add up … and your example does not.

The above naturally lends itself to a pivot table, but there are scripting things you can do if you have specific requirements that cannot be handled using QV the way it wants to be used.

Cheers,

Chris.

[NavinReddy]

Thanks for your brief explanation chris, jeshwanth19 answer helps me some what, that is what exactly im looking for, Thank you so much for your patience.

[Jesh19 · Accepted solution]

Hi Niranjan,

If you feel the answer helps you out, please close the thread by marking correct answer.

Thanks,

Jesh