Time Interval

[n1ef5ng1]

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

[n1ef5ng1]

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

[n1ef5ng1]

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

[rahulpawarb]

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