How to count entered and removed id based on date?

[silambarasan]

How to count entered and removed id based on date?

Input table

Id.      Date

1        1-jan

2.       1-jan

1.       2-jan

2.       2-jan

3.       2-jan

1.       3-jan

3.       3-jan

3.       4-jan

5        4-jan

2        5-jan

Expected output

Using pivot table count number of new ID  enter everyday and number of existing ID remove everyday

Note:

1.   if same day one new ID enter and existing ID remove then display count value as 1-1.

2.  Eg.  Id-2 enter on 1st jan and removed on 4th jan. and again it(ID-2)  enter on 5th jan, so id-2 consider as new id

Month.     Day.   1.     2.      3.        4      5

Jan.                   2      1       1       1-1      1

hic‌swuehl‌johnw‌gwassenaar‌

Message was edited by: Silambarasan M

[silambarasan]

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

[swuehl]

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]

Could you please explain Exit scripting code and LastWorkDate()

[swuehl]

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