Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

silambarasan
Contributor

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

Tags (2)
23 Replies
Highlighted
MVP
MVP

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

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

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

How on 3-jan new ID is 1?

MVP
MVP

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

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
Contributor

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

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

MVP
MVP

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

The difference is in calculating the exit flags.

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

silambarasan
Contributor

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

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

MVP
MVP

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

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

silambarasan
Contributor

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

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

MVP
MVP

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

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