How to lags categorical values of dimension

[lotitolo]

Hello everyone I'm looking to apply a lag effect on a dataset (and more precisely on categorical data).
This is in order to monitor status changes over the months accross à Sankey Chart.

First of all you can appreciate what the data looks like without lag effect:

Below are the values I would like to find where there are no data. 

I have already used this kind of calculation with Tableau using the raw data not transposed. Is it possible to do the same thing with Qlik ?

Tableau Formula :

if isnull(LOOKUP( ATTR( [Renewal_Contract] ),0 )) then PREVIOUS_VALUE( ATTR( [Renewal_Contract] ) ) else ATTR( [Renewal_Contract]) end

Raw Data :

Customer ID	Renewal_Contract	Date
A	C1	01/01/2019
B	C2	01/01/2019
C	C3	01/01/2019
C	C2	01/02/2019
D	C3	01/02/2019
E	C1	01/02/2019
F	C2	01/02/2019
A	C1	01/03/2019
B	C3	01/03/2019
C	C2	01/03/2019
D	C1	01/03/2019
E	C3	01/04/2019
F	C1	01/04/2019
A	C2	01/05/2019
C	C1	01/06/2019
D	C2	01/06/2019
E	C1	01/06/2019

If anyone has a pro tips.

Thank you.

Loïc

[shivanisapkale]

The lookup condition would not work because qlik wont be able to find the null entry for a particular customerid,as there is no entry of customerid for particular month.

For example: Customer Id 'A' has no entry in raw data for month 'Feb', 'Apr', 'Jun' hence it wont be able to find any null value for this customer id for these months so,your condition:" isnull(LOOKUP( ATTR( [Renewal_Contract] ),0 )) " will always be false.

So create the customer id entries for missing months in the raw data file.

[miskinmaz]

You need to handle the condition in front end pivot chart using previous function

[shivanisapkale]

Abc :
LOAD
"Customer ID",
Renewal_Contract,
"Date",
if(
"Customer ID"=Previous("Customer ID"),
if(
IsNull(Renewal_Contract),
Previous(Renewal_Contract),
Renewal_Contract
),
Renewal_Contract
) as FLAg
FROM [lib://lagged data/lagged data.xlsx]
(ooxml, embedded labels, table is [Raw Data]);

This is what i tried using the previous function but it is not getting the null values to be filled with this previous function so we need to add the data first in raw data file.

[lotitolo]

Thank you for all these first solutions.

A colleague of mine has given me a solution (using data transposition and next using case) , but it is impossible to maintain. A VlookUp function is really missing in this case. I'm not going to do my data preparation with Tableau and then use Qlik  😂.

I wonder if it is possible to optimize this code and just create a loop that does the job for all the months.

AUX:
Load * Inline [
Customer ID, Date,Renewal_Contract
A,01/01/2019,C1
B,01/01/2019,C2
C,01/01/2019,C3
C,01/02/2019,C2
D,01/02/2019,C3
E,01/02/2019,C1
F,01/02/2019,C2
A,01/03/2019,C1
B,01/03/2019,C3
C,01/03/2019,C2
D,01/03/2019,C1
E,01/04/2019,C3
F,01/04/2019,C1
A,01/05/2019,C2
C,01/06/2019,C1
D,01/06/2019,C2
E,01/06/2019,C1
]
;

NoConcatenate
AUX2:
Load
"Customer ID",
month(Date(Date#(Date,'DD/MM/YYYY'),'DD.MM.YYYY')) as Month,
Renewal_Contract
Resident AUX;

Drop table AUX;

AUX3:
Load distinct "Customer ID" Resident AUX2;

GenericTable:
Generic LOAD "Customer ID", Month, Renewal_Contract Resident AUX2;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'GenericTable.*') THEN

LEFT JOIN (AUX3) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];
ENDIF
NEXT

Drop table AUX2;
// Drop table AUX3;

NoConcatenate

AuxFinal:
Load
"Customer ID",
Jan,
if(len(Feb)>0,Feb,alt(Jan)) as Feb,
Mar,
Apr,
May,
Jun
Resident AUX3;

NoConcatenate

AuxFinal2:
Load
"Customer ID",
Jan,
Feb,
if(len(Mar)>0,Mar,alt(Jan,Feb)) as Mar,
Apr,
May,
Jun
Resident AuxFinal;

NoConcatenate

AuxFinal3:
Load
"Customer ID",
Jan,
Feb,
Mar,
if(len(Apr)>0,Apr,alt(Jan,Feb,Mar)) as Apr,
May,
Jun
Resident AuxFinal2;

NoConcatenate

AuxFinal4:
Load
"Customer ID",
Jan,
Feb,
Mar,
Apr,
if(len(May)>0,May,alt(Jan,Feb,Mar,Apr)) as May,
Jun
Resident AuxFinal3;

NoConcatenate

AuxFinal5:
Load
"Customer ID",
Jan,
Feb,
Mar,
Apr,
May,
if(len(Jun)>0,Jun,alt(Jan,Feb,Mar,Apr,May)) as Jun
Resident AuxFinal4;

Drop table AUX3;
Drop table AuxFinal;
Drop table AuxFinal2;
Drop table AuxFinal3;
Drop table AuxFinal4;