Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Dimension: Union of 2 Columns

I'd like to get the percentage of something happening based on two dimensions.

For example, I a table looked like this:

FruitA             FruitB            Result

Apples           Oranges          1

Bananas         Pears             1

Pears             Apples            0

My desired result would look like this

Dimension      Measure

Apples              50%

Oranges           100%

Bananas           100%

Pears                50%

----------------------------------------------

If it helps I've worked out the percentage of something happening based on one column:

measure  = (sum(Result)/count(total <FruitA> Won))*100

dimension = FruitA

This returns a result of:

Dimension      Measure

Apples               100%

Bananas             100%

Pears                   0%

1 Solution

Accepted Solutions
undergrinder
Valued Contributor II

Re: Dimension: Union of 2 Columns

Hi Stephen,

Kaushik thought as concatenate not the fields, but the columns.

The script look like that:

     Concatenated_fruits:

          Load

               FruitA as Fruit

              ,Result

          From/Resident YourTable;

     Load

             FruitB as Fruit

             ,Result

     From/Resident YourTable;

G.

6 Replies

Re: Dimension: Union of 2 Columns

Hi Stephen,

You will have to concatenate them and make one dimension in script, in order to achieve this.

Regards,

Kaushik Solanki

Not applicable

Re: Dimension: Union of 2 Columns

Hi Kaushik,

Thanks for your reply, however I don't understand your answer - If I were to concatenate the 1st row in the sample table the result would be "ApplesOranges"     1  - this isn't the desired result. Or am I misinterpreted your answer?

Kind Regards,

Stephen

undergrinder
Valued Contributor II

Re: Dimension: Union of 2 Columns

Hi Stephen,

Kaushik thought as concatenate not the fields, but the columns.

The script look like that:

     Concatenated_fruits:

          Load

               FruitA as Fruit

              ,Result

          From/Resident YourTable;

     Load

             FruitB as Fruit

             ,Result

     From/Resident YourTable;

G.

undergrinder
Valued Contributor II

Re: Dimension: Union of 2 Columns

If you the combination and order of FruitA and FruitB matters, than you can concatenated the columns, as you mentioned ("Apples|Oranges" rather than "ApplesOranges"), calculate the percentage, afterwhile arrange the columns, with the manner in my and Kaushik previus comment.

G.

reddys310
Honored Contributor II

Re: Dimension: Union of 2 Columns

Hi Stephen,

Concatenation works the same as Union in Qlik. So if you want to union two tables you can make use of hte concatenate function to do so.

Thanks,

Sangram.

Not applicable

Re: Dimension: Union of 2 Columns

Thanks Guys - that worked perfectly.