if statement on multiple columns return all values

[lbunnell]

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!

[krishnacbe]

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]

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]

[Peter_Cammaert]

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 · Accepted solution]

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.

[lbunnell]

Thank you!

[lbunnell]

Thanks, I appreciate the detail!

[lbunnell]

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?