# 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
Did you mean:
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
Contributor III

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)
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.

10 Replies
Partner

Hi

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

MVP

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

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.

Creator
Author

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

Contributor III

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)
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.

Creator
Author

Thank you!

Creator
Author

Thanks, I appreciate the detail!

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?

Community Browser