topic Re: Pick the right date base on condition in QlikView

Pick the right date base on condition

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Need some help with how can this be achieve in Qlikview

I have records as below. Basically, one policy number will have multiple transaction code. Transaction code NEW and REN will determine the DESIREDDATE for the rest of the transaction code. Sample as below.

Policy_number	Transaction_Code	Transaction_Date_1	DESIREDDATE
HSHC000001	NEW	20110926	20110926
HSHC000001	END	20130926	20110926
HSHC000001	REN	20140926	20140926
HSHC000001	END	20140705	20140926
HSHC000001	CAN	20151005	20140926

Any help is very much appreciated!

Re: Pick the right date base on condition

swuehl — Sat, 12 Mar 2016 20:48:38 GMT

Maybe something along these lines

INPUT:
LOAD * INLINE [
Policy_number, Transaction_Code, Transaction_Date_1, 
HSHC000001, NEW, 20110926 
HSHC000001, END, 20130926 
HSHC000001, REN, 20140926 
HSHC000001, CAN, 20151005 
];
RESULT:
LOAD 
 *,
 If(Match(Transaction_Code,'NEW','REN'), Transaction_Date_1, Peek('DESIREDDATE')) as DESIREDDATE
Resident INPUT
ORDER BY Transaction_Date_1;
DROP TABLE INPUT;

Re: Pick the right date base on condition

jagan — Mon, 14 Mar 2016 02:10:03 GMT

Hi,

Try this slightly modified code from swuehl, we need to use Policy_number in the Order by, I think you will have multiple Policy numbers

INPUT:
LOAD * INLINE [
Policy_number, Transaction_Code, Transaction_Date_1,
HSHC000001, NEW, 20110926
HSHC000001, END, 20130926
HSHC000001, REN, 20140926
HSHC000001, CAN, 20151005
];
RESULT:
LOAD
*,
If(Match(Transaction_Code,'NEW','REN'), Transaction_Date_1, Peek('DESIREDDATE')) as DESIREDDATE
Resident INPUT
ORDER BY Policy_number, Transaction_Date_1;
DROP TABLE INPUT;

Re: Pick the right date base on condition

Mon, 14 Mar 2016 02:50:04 GMT

Hi Jagan,

The idea is right. But what if i need to assign the desired date to more than one row? Is there anyway to make it a running number base on the number of rows for the policy number?

Regards,

Samuel

Re: Pick the right date base on condition

jagan — Mon, 14 Mar 2016 08:45:54 GMT

Is it not working? It will assign the data as long as it not changed to NEW OR REN,

Regards,

jagan