Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lotitolo
Contributor III
Contributor III

How to lags categorical values of dimension

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:

clipboard_image_0.png

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

clipboard_image_1.png

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 IDRenewal_ContractDate
AC101/01/2019
BC201/01/2019
CC301/01/2019
CC201/02/2019
DC301/02/2019
EC101/02/2019
FC201/02/2019
AC101/03/2019
BC301/03/2019
CC201/03/2019
DC101/03/2019
EC301/04/2019
FC101/04/2019
AC201/05/2019
CC101/06/2019
DC201/06/2019
EC101/06/2019

 

If anyone has a pro tips.

Thank you.

Loïc

Labels (2)
4 Replies
shivanisapkale
Partner
Partner

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
Creator III
Creator III

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

shivanisapkale
Partner
Partner

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
Contributor III
Contributor III

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;

 

clipboard_image_0.png