23 Replies Latest reply: May 11, 2016 2:25 PM by Stefan Wühl

# 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

Message was edited by: Silambarasan M

• ###### 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)

• ###### 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;

• ###### 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

• ###### 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?

• ###### 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

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

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

• ###### 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

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

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

You might also want to look into something like described here:

New/Lost/Returning/Loyal Customers

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

No i am getting wrong values.

 1-jan-2016 2 0 2-jan-2016 1 1 3-jan-2016 0 1 4-jan-2016 1 2 5-jan-2016 1 0

Expected table:

Day    add     exit

1           2       0

2          1        0

3          0        1

4          1        1

5           1       2

Day 5 :  two ids removed and one id enter

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

Have a look at the sample, I've attached. You need to use CountDate as dimension in your chart table.

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

please attach that file

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

I already had in my second last answer, but reattaching to this one.

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

Every thing works fine,but one modification.

If any ID removed on saturday and sunday,then we have to show that ids are removed on monday not saturday/sunday.

Instead of saturday and sunday, ids are removed on monday

please help this one.

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

You can account for this using LastWorkDate( Date, 1):

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, 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;

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

NO its not working.

Field values are not loaded.

Data set:

1, 4-jan-2016 (monday)

2, 4-jan-2016

1, 5-jan-2016 (tuesday)

2, 5-jan-2016

3, 5-jan-2016

1, 6-jan-2016 (wed)

3, 6-jan-2016

3, 7-jan-2016(thr)

5, 7-jan-2016

2, 8-jan-2016 (fri)

6, 11-jan-2016(mon)

Expected Output:

Date              New      Exit

4-jan-2016      2            0

5-jan-2016      1            0

6-jan-2016      0            1

7-jan-2016      1            1

8-jan-2016      1            2

11-jan-2016     1           1

Note:  9-jan and 10-jan no id's are enter.

on 8-jan-2016  id 2 is enter and we have to show that id is removed on monday not saturday and sunday

please help with attachment

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

This is what I get with my script (can't easily attach a QVW at the Moment):

 CountDate Sum(New) Sum(Exit) 04.01.2016 2 0 05.01.2016 1 0 06.01.2016 0 1 07.01.2016 1 1 08.01.2016 1 2 11.01.2016 1 1

Can't really see a difference to your expected result

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

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

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

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;

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

Could you please explain Exit scripting code and LastWorkDate()

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

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/lastworkdate.htm?q=la…

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

How on 3-jan new ID is 1?

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

yes