topic Re: if statement on multiple columns return all values in QlikView

if statement on multiple columns return all values

lbunnell — Fri, 27 May 2016 14:07:56 GMT

C1	C2	C3	C4
1	1	0	0
0	1	1	1
1	1	1	0

This is probably really simple, but I'm having a brain freeze on how to accomplish it. I need a logic statement that returns all of the values of 1. If I use an IF statement, the first if knocks out the subsequent values

This statement

if(C1=1,1,0,if(C2=1,1,0,if(C3=1,1,0,if(C4=1,1,0)

returns

count of C1 = 2

count of C2 = 1

count of C3 = 0

count of C4 = 0

What can I use to get the counts to accurately reflect

count of C1 = 2

count of C2 = 3

count of C3 = 2

count of C4 =1

Thanks!

Re: if statement on multiple columns return all values

krishnacbe — Fri, 27 May 2016 16:00:53 GMT

Can you post sample app to understand where you are using the IF statement?

Re: if statement on multiple columns return all values

sunny_talwar — Fri, 27 May 2016 16:06:49 GMT

Not sure how your data looks, but why can't you just do Sum(C1), Sum(C2), Sum(C3), Sum(C4)

Re: if statement on multiple columns return all values

lbunnell — Fri, 27 May 2016 17:48:39 GMT

Sorry, should have been more clear what I was trying to achieve. I am creating a Dimension for a chart that looked something like the IF statement above.

Re: if statement on multiple columns return all values

lbunnell — Tue, 31 May 2016 19:46:32 GMT

Re: if statement on multiple columns return all values

Peter_Cammaert — Tue, 31 May 2016 20:06:16 GMT

Two alternatives:

in your load script, you could use a CROSSTABLE load to serialize the data and tag it with the column header ('C1', C2', 'C3', etc.) Afterwards, you'll have a real dimension that makes your chart evolve with selections using a single sum(Value)
or create as many expressions in your chart as there are Cn-columns. This is usually a fixed number.that doesn't need updating. Similar to what Sunny suggested.

Best,

Peter

Re: if statement on multiple columns return all values

novolouy — Tue, 31 May 2016 22:04:17 GMT

Hi Lawrence(lbunnell‌),

You can achieve this by using the CROSSTABLE load:

In order to get to the above chart I have put your example table into an Excel file and loaded it using the CROSSTABLE load:

Table:
CROSSTABLE(Label,Value)
LOAD 
     RowNo() AS Row,
     C1, 
     C2, 
     C3, 
     C4
     
FROM
[SOURCE.xlsx]
(ooxml, embedded labels, table is EVENTDATE);

By using the CROSSTABLE load, you are pivoting your column labels to be used as a value, essentially converting your table to the following:

So after you have the above table, you can create a chart with the Dimension "Label" and the Expression: "SUM(Value)".

And that's it. I hope this was what you were looking for and it was helpful to you.

Please find attached the example application and the Excel file used in this test.

Regards.

Re: if statement on multiple columns return all values

lbunnell — Wed, 01 Jun 2016 12:56:29 GMT

Thank you!

Re: if statement on multiple columns return all values

lbunnell — Wed, 01 Jun 2016 12:56:59 GMT

Thanks, I appreciate the detail!

Re: if statement on multiple columns return all values

lbunnell — Wed, 01 Jun 2016 13:31:17 GMT

One more question... if I want to use the CrossTable with a larger data source that includes and is related to the User, userID, etc. would you be able to join the data table with the CrossTable?

Re: if statement on multiple columns return all values

novolouy — Wed, 01 Jun 2016 16:29:45 GMT

Hi Lawrence,

It is exactly the same thing.

The CROSSTABLE Load keeps the first column as is, and pivots the following columns.

So if I have the following table:

User ID	C1	C2	C3	C4
LukeS	1	1	0	0
JohnQ	1	1	1	0
HanS	0	1	1	1

And I apply the same CROSSTABLE Load I will have the following table in QlikView:

And I can create the same chart and have the same results as shown before.

If you want to add any additional qualifier columns, you will have to add a parameter in the CROSSTABLE Load.

For example, if in addition to "User ID" I also want to have "Region" I use the following load example:

Table:
CROSSTABLE(Label,Value, 2)
LOAD 
     [User ID],
     Region,
     C1, 
     C2, 
     C3, 
     C4
     
FROM

(ooxml, embedded labels, table is EVENTDATE);

That additional parameter (the number 2) at the end of CROSSTABLE defines how many columns are not to be pivoted. By default is 1, the first one. By defining it as 2, I am telling it not to pivot "User ID" nor "Region".

You can escalate that as you require.

Attached is an example with User ID.

Hope this was helpful.