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

[swuehl]

Maybe like

INPUT:
LOAD Id, Date#(Date,'D-MMM') as Date INLINE [
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
];


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


RESULT:
LOAD Id, Date, Exit, If(previous(Id)<>Id or previous(Date)+1<>Date,1) as New
RESIDENT TMP
ORDER BY Id, Date asc;


DROP TABLE INPUT, TMP;

Then create a chart with dimension Date and

=Sum(New) & '-' & Sum(Exit)

[MK_QSL]

How on 3-jan new ID is 1?

[swuehl]

May previous script code counts Ids on their last date recorded as 'Exit'.

If you don't want to do this, so it assigns 'Exit' flag only on intermediate dates, you can change the TMP table script to:

TMP:

LOAD Id, Date, If(previous(Id)=Id and previous(Date)-1<>Date,1) as Exit

RESIDENT INPUT

ORDER BY Id, Date Desc;

[silambarasan]

output should be

Day    add     exit

1           2       0

2          1        0

3          0        1

4          1        1

5           1       0

but your value is slightly change

[swuehl]

The difference is in calculating the exit flags.

Can you elaborate a bit more detailed, when you need to create the exit flag?

[silambarasan]

Day one :ID -1,2 are enter,so count of entered id is two

Day  two: Id-3 are enter,so count is one

Day three: Id-2 removed ,so count of removed id is 1 and no new id enter so count of enter id is zero

Day four  : id-1 removed and id-5 enter so 1 & 1 which means count of enter id and count of removed id

Day five : id-2 again enter ,so count of new enter id is one

now table is

Day    add     exit

1           2       0

2          1        0

3          0        1

4          1        1

5           1       0

[swuehl]

Day 5, IDs 3 and 5 are gone? Exit = 2?

[silambarasan]

Yes i am sorry I made a mistake

Day 5 ID 3 and 5 are gone and

New ID 2 is add

Day    add     exit

1           2       0

2          1        0

3          0        1

4          1        1

5           1       2

[swuehl]

Maybe like this:

INPUT:
LOAD Id, SetDateYear(Date#(Date,'D-MMM'),2016) as Date INLINE [
 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
];


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

RESULT:
LOAD Id, Date, Date(Date+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;

I've changed the Exit calculation code so that it creates the exit flag on the following date of the last visit (on a date where your original data shows no entry for that Id, of course).