Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
silambarasan
Creator II
Creator II

How to count entered and removed id based on date?

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

hicswuehljohnwgwassenaar

Message was edited by: Silambarasan M

23 Replies
swuehl
MVP
MVP

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)

NewExit.png

MK_QSL
MVP
MVP

How on 3-jan new ID is 1?

swuehl
MVP
MVP

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
Creator II
Creator II
Author

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

The difference is in calculating the exit flags.

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

silambarasan
Creator II
Creator II
Author

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

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

silambarasan
Creator II
Creator II
Author

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

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