topic Re: How to count the missing ID count for missing date? in QlikView

How to count the missing ID count for missing date?

silambarasan — Sat, 05 Mar 2016 16:01:21 GMT

How to count the missing ID count for missing date?

Input Table :

ID Start_Date End_Date

A1 2-jan-2016 20-jan-2016

A1 22-jan-2016 28-jan-2016

B1 3-Feb-2016 20-Feb-2016

B1 22-Feb-2016 25-Feb-2016

C1 2-jan-2016 20-jan-2016

C1 22-jan-2016 31-jan-2016

Expected output:

Month	Day	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
Jan-16																						2								1	1	1
Feb-16																						1					1	1	1	1

Example:

ID - A1 starting date is 2-jan and end in 20-jan and again 22-jan-2016 the ID(A1) start and end 28-jan-2016 . In between 21-jan-2016 , Id(A1) is missing.so missing date is 21-jan-2016. we have to show the count of missing ID. For jan month 31 days are there, so fill the missing count for remaining date.(Refer Expected output). 21-jan-2016 two id(A1, C1) are missing.So count of missing on 21-jan-2016 is 2

Note:First time ID Start date is not consider for missing count.

tresesco‌hic‌NareshGuntur‌ziadmohd1‌QlikView Forums & Resources‌Scripting‌Blogs‌

Re: How to count the missing ID count for missing date?

johnw — Sat, 05 Mar 2016 19:02:51 GMT

It looks to me that an ID is not considered missing before the first start date, or after the last end date?

I might load my data into this sort of structure:

ID Date Present Missing

A1 2-jan-2016 1 0

A1 3-jan-2016 1 0

...

A1 20-jan-2016 1 0

A1 21-jan-2016 0 1

A1 22-jan-2016 1 0

...

A1 28-jan-2016 1 0

B1 3-feb-2016 1 0

B1 4-feb-2016 1 0

...

B1 20-feb-2016 1 0

B1 21-feb-2016 0 1

B1 22-feb-2016 1 0

etc.

Or have a flag, or a status field, whatever you find most useful. Or more than one way of identifying the same information. The above doesn't lend itself to selections, for instance, only for counting up in a chart with sum(Missing). A Y/N flag of "Missing?" could be done with count({<Missing*={'Y'}>} distinct ID). A status of Present/Missing could be done with count({<Status*={'Missing'}>} distinct ID). Or even with a flag or status field, you could have a Missing field to sum up like that. These also give different results if, say, you're adding up what's missing for a Month. Do you want the number of ID-days that are missing? Just the number of IDs? The sum() gives a different answer than the count() in this case.

Then have a calendar that gives you the month and day for each date.

Then make a pivot table with Month and Day as dimensions, and use one of the expressions above.

Transforming your input data into what I show above may be difficult depending on your experience. If this looks like the solution, give it a shot. If you can't figure it out, I'm sure I can figure out a script to do it.

Re: How to count the missing ID count for missing date?

MarcoWedel — Sun, 06 Mar 2016 01:48:33 GMT

Hi,

similar to John's suggestion maybe one solution could be:

table1:
LOAD * INLINE [
    ID, Start_Date, End_Date
    A1, 2-jan-2016, 20-jan-2016
    A1, 22-jan-2016, 28-jan-2016
    B1, 3-Feb-2016, 20-Feb-2016
    B1, 22-Feb-2016, 25-Feb-2016
    C1, 2-jan-2016, 20-jan-2016
    C1, 22-jan-2016, 31-jan-2016
];

tabDateLink:
LOAD ID,
    Date(Start_Date+IterNo()-1) as Date,
    1 as DatePresent
Resident table1
While Start_Date+IterNo()-1 <= End_Date;

LOAD ID,
    Date(End_Date+IterNo()) as Date,
    0 as DatePresent
While End_Date+IterNo()<If(ID=PrevID, PrevStart, MonthEnd(End_Date));   
LOAD *, Previous(ID) as PrevID, Previous(Start_Date) as PrevStart
Resident table1
Order By ID, Start_Date desc;

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Start_Date) as MinDate, 
    Today() as MaxDate 
Resident table1;

hope this helps

regards

Marco

Re: How to count the missing ID count for missing date?

silambarasan — Sun, 06 Mar 2016 12:06:17 GMT

Count Missing Id's per date. Exact answer like Marco wedel.

Re: How to count the missing ID count for missing date?

MarcoWedel — Sun, 06 Mar 2016 15:27:55 GMT

please close your thread if your question is answered.

thanks

regards

Marco

Re: How to count the missing ID count for missing date?

silambarasan — Mon, 07 Mar 2016 06:40:51 GMT

I closed thread,once i practically work

Re: How to count the missing ID count for missing date?

silambarasan — Tue, 08 Mar 2016 08:30:15 GMT

Marco

How the above scripts works please explain?

Re: How to count the missing ID count for missing date?

silambarasan — Tue, 08 Mar 2016 08:34:28 GMT

Little extra works left Marco.

A1 start 2-jan-2016 and end 28-jan-2016.

Now i want,the maximum calendar date is 25-feb-2016 .so, I want to show the ID A1 is missing upto maximum calendar date.please reply

Re: How to count the missing ID count for missing date?

silambarasan — Wed, 09 Mar 2016 11:49:56 GMT

Little extra works left Marco.

A1 start 2-jan-2016 and end 28-jan-2016.

Now i want,the maximum calendar date is 25-feb-2016 .so, I want to show the ID A1 is missing upto maximum calendar date.please reply

How to count the missing ID count for missing date?

Re: How to count the missing ID count for missing date?

Saravanan_Desingh — Thu, 31 Mar 2016 19:49:48 GMT

Hi Simbu,

I'm not sure, if you got the solution.

I think, you can add the following line to your Data:

table1:

LOAD * INLINE [

ID, Start_Date, End_Date

A1, 2-jan-2016, 20-jan-2016

A1, 22-jan-2016, 28-jan-2016

A1, 29-jan-2016, 25-feb-2016

B1, 3-Feb-2016, 20-Feb-2016

B1, 22-Feb-2016, 25-Feb-2016

C1, 2-jan-2016, 20-jan-2016

C1, 22-jan-2016, 31-jan-2016

];