topic Re: Count if value does not exist in all previous rows in QlikView

Count if value does not exist in all previous rows

sifatnabil — Thu, 10 Sep 2015 08:41:01 GMT

Hi all,

I have a time chart of values, i.e. time is dimension and count(distinct Value) in expression. How do I count in chart only the values that never appeared in all previous rows? e.g. from [Jan to Dec], if Value='xyz' appeared in Feb, and then in March, in the time chart, it should only count xyz in Feb and not again in March.

Data looks like this:

The chart should be like this:

Re: Count if value does not exist in all previous rows

MK_QSL — Thu, 10 Sep 2015 08:43:45 GMT

You need to count this in script itselt.. !

Re: Count if value does not exist in all previous rows

sifatnabil — Thu, 10 Sep 2015 08:49:01 GMT

Thanks MRKachhiaIMP‌, how would I do this in script when each row in script represents a Value and a single Date? I don't have the simplified monthly view.

e.g.

Date, Value

5/10/2015, xrw

5/10/2015, gtg

5/10/2015, sssw

6/10/2015, fdg

I am not sure how to use peek or previous to check if value exists in previous month when the script is in single dates.

Re: Count if value does not exist in all previous rows

MK_QSL — Thu, 10 Sep 2015 09:17:00 GMT

Temp:
Load
  Date(Date#(Date,'M/DD/YYYY')) as Date,
  Value
Inline
[
  Date, Value
  1/10/2015, abc
  1/10/2015, adf
  1/10/2015, aer
  2/10/2015, abc
  2/10/2015, adf
  2/10/2015, aer
  2/10/2015, xyz
  3/10/2015, abc
  3/10/2015, adf
  3/10/2015, aer
  3/10/2015, xyz
  4/10/2015, abc
  4/10/2015, adf
  4/10/2015, aer
  4/10/2015, xyz
  4/10/2015, yyy
];


Final:
Load
  Value,
  If(Previous(Value)=Value,0,1) as Count,
  Month(Date) as Month,
  Date 
Resident Temp
Order By Value, Date;


Drop Table Temp;

Now create a Straight Table

Dimension

Month

Expression

SUM(Count)

Presentation Tab..

Untick Suppress Zero Values

Re: Count if value does not exist in all previous rows

sifatnabil — Mon, 14 Sep 2015 04:30:20 GMT

Thanks! This worked. I understood that sorting by Value and then Date made the previous() function work correctly.