straight table expression

[vireshkolagimat]

Hi Guys,

I have a table which contains the information like below.

date       ID   ID1   ID2   ID3

1/1/2015 34

1/2/2015 55   21

3/2/2015 21   67    54

I want to show this information in straight table with date as dimension.

If the ID only exists for a date, then it should give the count as 1 else it should look for the all the 4 columns and give me the count.

For example:

date        ID count

1/1/2015  1

1/2/2015  2

1/3/2013  3

Regards,

Viresh

[sunny_talwar]

Try this expression:

=RangeCount(Avg(ID), Avg(ID1), Avg(ID2), Avg(ID3))

[vireshkolagimat]

Hi, thanks for the reply.

How to achieve the below requirement.

If there is value in only ID column and no value in ID1, ID2, ID3 then i should get as primary.

If there is any value in any one of the three columns i.e ID1, ID2, ID3 along with ID column then i want to show as secondary.

please let me know how to achieve this in script editor.

Regards,

Viresh

[sunny_talwar]

Not sure I understand what you are trying to do. Can you give an example?

[vireshkolagimat]

If you look at the first row, for cust ID 123, i have only one acc ID so i want to show as Primary customer.

In other cases, if the Cust ID have more than on Acc ID apart from Acc ID, then i want to show as secondary customer.

I have shown expected result in the last column (Colored one).

[jagan]

Hi,

Try like this

LOAD

*,

If(Len(Trim(ID1)) = 0 AND Len(Trim(ID2)) = 0 AND Len(Trim(ID3)) = 0, 'Primary', 'Secondary') AS Type

FROM DataSource;

Regards,

jagan.

[sunny_talwar]

May be something like this:

If(RangeCount([Acc ID1], [Acc ID2], [Acc ID3]) > 0, 'Secondary', 'Primary') as NewField

[vireshkolagimat]

thank you. Its working as expected..

[sunny_talwar]

Awesome