topic Re: Combine three fields into one filed in QlikView

Combine three fields into one filed

NavinReddy — Tue, 30 Oct 2018 16:05:57 GMT

Hello All,

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

Org Level1
Org Level2
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

Re: Combine three fields into one filed

jobsonkjoseph — Tue, 30 Oct 2018 16:13:08 GMT

Hi Niranjan,

The question is not so clear, but for drop down you can use MultiBox object.

Re: Combine three fields into one filed

NavinReddy — Tue, 30 Oct 2018 16:42:29 GMT

Hi Jobson,

Thanks for your reply,

But I want to use this field in chats, in single field i need to show this drop down.

Thanks,

Re: Combine three fields into one filed

jobsonkjoseph — Tue, 30 Oct 2018 16:51:01 GMT

ok.

if the data can be shared, i mean atleast dummy data, we can try to figure out.

Re: Combine three fields into one filed

chrismarlow — Tue, 30 Oct 2018 17:21:34 GMT

Niranjan,

If these form a hierarchy and you really need to be able to show all levels at the same time, ignoring the hierarchy relationship then I think you will have to do some scripting to come up with a 1 to many mapping for each of your Org LevelX to associate with your fact table.

If it is the searchable nature of the hierarchy that is more important you could use a search object with the three fields added & a drill down group on the chart (with the 3 fields) that would then show most appropriate level depending on selection.

As Jobson suggests a few more details needed to help further.

Regards,

Chris.

Re: Combine three fields into one filed

Jesh19 — Tue, 30 Oct 2018 17:25:57 GMT

Please elaborate your question.

Are these 3 (Org Level1, Org Level2,Org Level3) different fields? or values under some field.

I think you can make use of multibox.

Re: Combine three fields into one filed

NavinReddy — Tue, 30 Oct 2018 18:18:33 GMT

these are all 3 different fields, want to make it one field as per my requirement

Re: Combine three fields into one filed

chrismarlow — Tue, 30 Oct 2018 18:55:36 GMT

Niranjan,

We got that, but need to understand more about the other fields before making further suggestions.

Regards,

Chris.

Re: Combine three fields into one filed

NavinReddy — Wed, 31 Oct 2018 04:44:01 GMT

Hi Chris,

Using 3 fields i have to create below straight table

LOB	Q1'2018	Q2'2018	Q3'2018
Org Level1	45	56	87
Org Level12	8	26	10
Org Level3	10	-4	-67

Thanks & Regards,

Re: Combine three fields into one filed

nasirsaikh — Wed, 31 Oct 2018 05:46:46 GMT

Use Concat function

Re: Combine three fields into one filed

jobsonkjoseph — Wed, 31 Oct 2018 07:45:09 GMT

Hi Niranjan,

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

Have you tried this instead.

Re: Combine three fields into one filed

NavinReddy — Wed, 31 Oct 2018 10:06:15 GMT

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

Re: Combine three fields into one filed

Jesh19 — Wed, 31 Oct 2018 12:16:35 GMT

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

Re: Combine three fields into one filed

chrismarlow — Wed, 31 Oct 2018 16:01:52 GMT

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.

Re: Combine three fields into one filed

NavinReddy — Wed, 31 Oct 2018 18:45:04 GMT

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.

Re: Combine three fields into one filed

Jesh19 — Thu, 01 Nov 2018 03:30:53 GMT

Hi Niranjan,

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

Thanks,

Jesh