Create flag based on data from multiple prior dates?

[#1]

Hello -

I am trying to create a FLAG (0 or 1) within my LOAD script based on several prior weeks of data. Here is a sample data set:

Group	Date	Measure
A	12/1/2015	1
A	12/2/2015	0
A	12/3/2015	1
A	12/4/2015	1
A	12/5/2015	0
A	12/6/2015	1
A	12/7/2015	1
A	12/8/2015	1
A	12/9/2015	0
A	12/10/2015	0
A	12/11/2015	0
A	12/12/2015	1
A	12/13/2015	0
A	12/14/2015	1
A	12/15/2015	0
A	12/16/2015	1
A	12/17/2015	1
A	12/18/2015	0
A	12/19/2015	1
A	12/20/2015	1
A	12/21/2015	1
A	12/22/2015	0

I want to add a field called FLAG which, for every date, will look at itself and also back at the prior two weeks and if all three MEASUREs are 1 then the FLAG will be 1. If any measure if not 1, then the FLAG will be 0.

For example, on 12/22/2015, the FLAG would be 0 because 12/22/2015 is 0, 12/15/2015 is 0 and 12/8/2015 is 1.

The FLAG on 12/21/2015 would be 1 because: 12/21/2015 is 1, 12/14/2015 is 1 and 12/7/2015 is 1.

I want to do this in the LOAD script because we have hundreds of millions of records and I'd like to not bog down the application, if possible. Otherwise, if you also know a solution via set analysis (or some other method/expression), I would gladly be open to suggestions .

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD Group,

    Date,

    Measure

FROM

[https://community.qlik.com/thread/197633]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If(Sum = 3, 1, 0) as Flag;

LOAD *,

  RangeSum(Measure, Previous(Measure), Previous(Previous(Measure))) as Sum

Resident Table

Order By Date;

DROP Table Table;

[maxgro]

Source:

load * inline [  

Group Date Measure

A 12/1/2015 1

A 12/2/2015 0

A 12/3/2015 1

A 12/4/2015 1

A 12/5/2015 0

A 12/6/2015 1

A 12/7/2015 1

A 12/8/2015 1

A 12/9/2015 0

A 12/10/2015 0

A 12/11/2015 0

A 12/12/2015 1

A 12/13/2015 0

A 12/14/2015 1

A 12/15/2015 0

A 12/16/2015 1

A 12/17/2015 1

A 12/18/2015 0

A 12/19/2015 1

A 12/20/2015 1

A 12/21/2015 1

A 12/22/2015 0

] (delimiter is spaces);

Left Join (Source) load Date+7 as Date, Group, Measure as Measure_1W, Date as Date_1W Resident Source;

Left Join (Source) load Date+14 as Date, Group, Measure as Measure_2W, Date as Date_2W Resident Source;

Left Join (Source) LOAD Date, Group, if(RangeSum(Measure, Measure_1W, Measure_2W)=3,1,0) as Flag  Resident Source;

[#4]

Bingo! You're the man, SunnyT. Thanks so much for the quick reply and your expertise!!

Out of curiosity, do you know a way to do something similar via expression?

[sunny_talwar]

This expression:

=If(RangeSum(Above(TOTAL Measure, 0, 3)) = 3, 1, 0)

and Group, Date, Measure as your dimension

Note: Both these solutions assume that the accumulation won't restart, meaning that if for instance you want to look at Group A and Group B separately then you will need to alter the back-end and front-end solution as per the change in the requirement.

[#6]

Can this be changed to look back at previous weeks, rather than previous days?

[sunny_talwar]

It can be, what is the exact requirement?