Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
jim_chan
Creator III
Creator III

Qlikview - how to get to create a flag

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.

jim_chan_0-1621667421702.png

I have attached a sample qvw for your reference.

 

Labels (1)
1 Solution

Accepted Solutions
jim_chan
Creator III
Creator III
Author

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. 

jim_chan_0-1621732340440.png

End Result

jim_chan_2-1621732954621.png

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).  

jim_chan_3-1621733135626.png

 

 

 

View solution in original post

3 Replies
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
;

talk is cheap, supply exceeds demand
jim_chan
Creator III
Creator III
Author

thanks. i will try it out now gysbert!

jim_chan
Creator III
Creator III
Author

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. 

jim_chan_0-1621732340440.png

End Result

jim_chan_2-1621732954621.png

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).  

jim_chan_3-1621733135626.png

 

 

 

View solution in original post