9 Replies Latest reply: Mar 31, 2016 3:50 PM by Saran De RSS

    How to count the missing ID count for  missing date?

    Silambarasan M

      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:

                                     

      MonthDay12345678910111213141516171819202122232425262728293031
      Jan-16 2111
      Feb-1611111

       

      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.

       

      tresescohicnareshgunturziadmohd1QlikView Forums & ResourcesScriptingBlogs

        • Re: How to count the missing ID count for  missing date?
          John Witherspoon

          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?
            Marco Wedel

            Hi,

             

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

             

            QlikCommunity_Thread_208298_Pic1.JPG

            QlikCommunity_Thread_208298_Pic3.JPG

            QlikCommunity_Thread_208298_Pic2.JPG

             

            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