Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Message was edited by: Silambarasan M
This is what i am also want but that script not works.Really do not know.please post script again
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;
Could you please explain Exit scripting code and LastWorkDate()
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.