Qlikview - how to get to create a flag

[jim_chan]

Dear all, 

Need your help on this. 

OK.  Problem:

i need to create a flag to flag out for 

ID to refer - 12345678

if usage dated_date & activation date  fall on the  same date and have usage(total_gb), and the remaining 7 days have 0 total_gb, this kind of ID have to flag as Y.

ID to refer - 13578901

if usage dated_date & activation date  not fall on the  same date and have usage(total_gb), and the remaining 7 days have  total_gb, this kind of ID have to flag as N.

ID to refer - 24689101

if usage dated_date & activation date  or not fall on the  same date and have usage(total_gb), and the remaining 7 days have  total_gb, this kind of ID have to flag as N.

I have used a script as below, 

if(DATED_DT-ACTIVATION_DT = 0 and TOTAL_GB > 0, 'Y') as Flag1. But it couldnt achieve my result. All IDs are flag as Y.

I have attached a sample qvw for your reference.

[Gysbert_Wassenaar]

Perhaps like this:

FINAL_DATA:

LOAD
	*,
	If(Flag1 = 'Y' 
		AND rangesum(peek(TOTAL_GB,-1)
					,peek(TOTAL_GB,-2)
					,peek(TOTAL_GB,-3)
					,peek(TOTAL_GB,-4)
					,peek(TOTAL_GB,-5)
					,peek(TOTAL_GB,-6)
					,peek(TOTAL_GB,-7)					
					) = 0, 'Y', 'N') as FlagFinal
	;
LOAD 
	 ID,
     Date(ACTIVATION_DT) as ACTIVATION_DT, 
     DATE(DATED_DT) as DATED_DT, 
     if(DATED_DT-ACTIVATION_DT = 0 and TOTAL_GB > 0, 'Y') as Flag1,
TOTAL_GB
RESIDENT DATA_TEMP
ORDER BY ID, DATED_DT desc
;

[jim_chan]

thanks. i will try it out now gysbert!

[jim_chan · Accepted solution]

Hi, 

Gysbert, when i create a table with ID and FlagFinal , found out id 12345678 has 2 diff flag value. is there anything else can change in the script. ? i have created the end result for flag Y here. 

End Result

ID that met the condition should all flag as Y.  so that , as result we know this This ID after the dated_date (same date activation date), has no total_GB(0 value).