topic Re: combine two columns in one single dimension in QlikView

combine two columns in one single dimension

jduluc12 — Tue, 04 Jul 2017 17:18:22 GMT

Hi,

I have a data set that looks like this

col1 col2 amount

A B 100

C D 200

i want to build a report which looks like this

col1+ col2 Amount

A 100

B 100

C 200

D 200

Any suggestion?

Re: combine two columns in one single dimension

vishsaggi — Tue, 04 Jul 2017 17:25:27 GMT

Try this?

LOAD col1 AS NewCol, amount INLINE [

col1, col2, amount

A , B, 100

C, D, 200

];

Concatenate

LOAD col2 AS NewCol, amount INLINE [

col1, col2, amount

A , B, 100

C, D, 200

];

Re: combine two columns in one single dimension

jduluc12 — Tue, 04 Jul 2017 19:22:38 GMT

Hey Vishwa

I do not have access to load script.I need to do it using expression.

Re: combine two columns in one single dimension

vishsaggi — Tue, 04 Jul 2017 19:39:58 GMT

I am not sure if that is possble, but what is your actual data looks like? can you share some of your actual data?

Re: combine two columns in one single dimension

MarcoWedel — Tue, 04 Jul 2017 23:56:41 GMT

Hi,

although it would be a way easier solution if you could create a combined field in the script, a front end only solution might be:

=ValueList($(=Concat(Distinct Chr(39)&col1&Chr(39),',')),$(=Concat(Distinct Chr(39)&col2&Chr(39),',')))

Sum(If(ValueList($(=Concat(Distinct Chr(39)&col1&Chr(39),',')),$(=Concat(Distinct Chr(39)&col2&Chr(39),',')))=col1,amount))+Sum(If(ValueList($(=Concat(Distinct Chr(39)&col1&Chr(39),',')),$(=Concat(Distinct Chr(39)&col2&Chr(39),',')))=col2,amount))

hope this helps

regards

Marco