Pick the right date base on condition

[#1]

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!

[swuehl]

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;

[jagan]

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

1. INPUT: 
2. LOAD * INLINE [ 
3. Policy_number, Transaction_Code, Transaction_Date_1,  
4. HSHC000001, NEW, 20110926  
5. HSHC000001, END, 20130926  
6. HSHC000001, REN, 20140926  
7. HSHC000001, CAN, 20151005  
8. ]; 
9. RESULT: 
10. LOAD  
11. *, 
12. If(Match(Transaction_Code,'NEW','REN'), Transaction_Date_1, Peek('DESIREDDATE')) as DESIREDDATE 
13. Resident INPUT 
14. ORDER BY Policy_number, Transaction_Date_1; 
15. DROP TABLE INPUT; 

[#4]

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

[jagan]

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

Regards,

jagan