Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
silambarasan
Contributor

Re: How to count entered and removed id based on date?

This is what i am also want but that script not works.Really do not know.please post script again

MVP
MVP

Re: How to count entered and removed id based on date?

INPUT: 
LOAD Id, Date#(Date,'D-MMM-YYYY') as Date INLINE
Id, Date 
1, 4-jan-2016
2, 4-jan-2016 
1, 5-jan-2016
2, 5-jan-2016
3, 5-jan-2016
1, 6-jan-2016
3, 6-jan-2016
3, 7-jan-2016
5, 7-jan-2016
2, 8-jan-2016
6, 11-jan-2016
]
;
 

Let vMaxDate = Peek('Date',-1,'INPUT'); 

RESULT: 
LOAD Id, Date, LastWorkDate( Date(Date+1),1) as CountDate, Exit 
WHERE Date <> '$(vMaxDate)'; 
LOAD Id, Date, If(previous(Id)<>Id or previous(Date)-1<>Date,1) as Exit 
RESIDENT INPUT 
ORDER BY Id, Date Desc

CONCATENATE 
LOAD Id, Date, Date(Date) as CountDate, If(previous(Id)<>Id or previous(Date)+1<>Date,1) as New 
RESIDENT INPUT 
ORDER BY Id, Date asc

DROP TABLE INPUT; 

silambarasan
Contributor

Re: How to count entered and removed id based on date?

Could you please explain Exit scripting code and LastWorkDate()

MVP
MVP

Re: How to count entered and removed id based on date?

It's basically two steps:


Create an table that is sorted by Id and Date desc. If there is a change in Id or Date differs from the previous record more than 1 day, that's indicating a not continued Id, hence set the Exit flag to 1.


LOAD Id, Date, If(previous(Id)<>Id or previous(Date)-1<>Date,1) as Exit 
RESIDENT INPUT 
ORDER BY Id, Date Desc


Then the preceding LOAD will be executed:


RESULT: 
LOAD Id, Date, LastWorkDate( Date(Date+1),1) as CountDate, Exit 
WHERE Date <> '$(vMaxDate)'; 


The where clause removes the very last date (i,e, for the very last date in our records, we don't know if Id is really discontinued or maybe it will appear again tomorrow with the next record). Adapt if this clause is not needed.

Then the date will be modified to the next working day using LastWorkDate() function and set as CountDate. Why to the next date? Because we are not interested at the last date where Id exists, but in the following working day where it doesn't exist for the first time.

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/l...

Community Browser