Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
lbunnell
Creator
Creator

if statement on multiple columns return all values

C1C2C3C4
1100
0111
1110

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!

1 Solution

Accepted Solutions
novolouy
Contributor III
Contributor III

Hi Lawrence(lbunnell‌),

You can achieve this by using the CROSSTABLE load:

Crosstable.png

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:

CrossedTable.png

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.

View solution in original post

10 Replies
krishnacbe
Partner
Partner

Hi

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

sunny_talwar

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

lbunnell
Creator
Creator
Author

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.

lbunnell
Creator
Creator
Author

Peter_Cammaert
Partner
Partner

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

novolouy
Contributor III
Contributor III

Hi Lawrence(lbunnell‌),

You can achieve this by using the CROSSTABLE load:

Crosstable.png

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:

CrossedTable.png

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.

View solution in original post

lbunnell
Creator
Creator
Author

Thank you!

lbunnell
Creator
Creator
Author

Thanks, I appreciate the detail!

lbunnell
Creator
Creator
Author

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?