3 Replies Latest reply: Jan 25, 2017 4:46 AM by Rahul Pawar RSS

    Time Interval

    ben lim

      Hi guys,

      i am trying to create a snapshot table in script to see how many are pending queries (measure = country_ID) at the time the data is reload

       

      I am coming out with fields like

      # of issues unresolved

      # of issued unresolved in < 2 days

      # of issues unresolved in >2  days

      # of issues unresolved in > 7 days

       

      count(DISTINCT if(status =unresolved and Date > date#(Date(Interval(Today()-3)),'DD/MM/YYYY'),Country_id)) as SS_BacklogTier0,

      //count(DISTINCT if(status =unresolved and Date > date#(Date(Interval(Today()-3)),'DD/MM/YYYY'),Country_id)) as SS_BacklogTier1,

      //count(DISTINCT if(status =unresolved and Date > date#(Date(Interval(Today()-8)),'DD/MM/YYYY'),Country_id)) as SS_BacklogTier2,

       

      i def. know that the data function works when i do an if statement on dimension table but it doesnt seems to get the right output. is my interval format wrong. my format for Date field is correct

        • Re: Time Interval
          ben lim

          the output number that i get from the three fields are identical numbers.

          • Re: Time Interval
            ben lim

            I solved. is Date not Date#... -_- can ignore this qn

            • Re: Time Interval
              Rahul Pawar

              Hello Ben,

               

              You can use below sample script:

               

              Sample:
              LOAD * INLINE [
              Country_id, Date, status, Flag
              1001, 01/01/2017, unresolved, 7
              1002, 01/05/2017, unresolved, 7
              1003, 01/09/2017, unresolved, 7
              1004, 01/22/2017, unresolved, >2
              1005, 01/24/2017, unresolved, <2
              1006, 01/24/2017, unresolved, <2
              ];
              
              
              SampleData:
              LOAD Country_id,
              count(DISTINCT if(status='unresolved' and Today()- Num(Date) > 7,Country_id)) as SS_BacklogTier0,
              count(DISTINCT if(status='unresolved' and (Today()- Num(Date) < 7 AND  Today()- Num(Date) > 2),Country_id)) as SS_BacklogTier1,
              count(DISTINCT if(status='unresolved' and  Today()- Num(Date) < 2,Country_id)) as SS_BacklogTier2
              Resident Sample
              GROUP BY Country_id;
              

               

              Thank you!

              Rahul