topic Re: straight table expression in QlikView

straight table expression

vireshkolagimat — Wed, 13 Jan 2016 16:23:30 GMT

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

Re: straight table expression

sunny_talwar — Wed, 13 Jan 2016 17:28:31 GMT

Try this expression:

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

Re: straight table expression

vireshkolagimat — Wed, 20 Jan 2016 14:44:48 GMT

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

Re: straight table expression

sunny_talwar — Wed, 20 Jan 2016 16:47:33 GMT

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

Re: straight table expression

vireshkolagimat — Thu, 21 Jan 2016 08:54:18 GMT

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

Re: straight table expression

jagan — Thu, 21 Jan 2016 09:06:37 GMT

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.

Re: straight table expression

sunny_talwar — Thu, 21 Jan 2016 11:07:44 GMT

May be something like this:

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

Re: straight table expression

vireshkolagimat — Thu, 21 Jan 2016 13:14:52 GMT

thank you. Its working as expected..

Re: straight table expression

sunny_talwar — Thu, 21 Jan 2016 18:05:06 GMT

Awesome