topic Re: DD:HH:MM format in QlikView

DD:HH:MM format

karthikoffi27se — Wed, 25 Nov 2020 16:16:04 GMT

I have the data where it is in DD:HH:MM format, now I need to use a if condition to extract anything less than <= 7 days and <= 5 days and <= 4 hours. Can you help me to achieve this please?

Many Thanks

Karthik

Re: DD:HH:MM format

tresB — Tue, 29 May 2018 05:19:58 GMT

Your if condition looks to be not so convincing. Anything <=4 hours would always satisfy your other two conditions. Hence, checking only the last condition would be enough. Could you post a sample data set and expected output to understand your requirement better?

Re: DD:HH:MM format

shiveshsingh — Tue, 29 May 2018 05:23:51 GMT

can you post sample data and expected output?

Re: DD:HH:MM format

karthikoffi27se — Tue, 29 May 2018 05:44:38 GMT

I have attached the sample data, My output should be like >5 days <= 7 days I need to count request ID for this interval this pattern is followed for remaining if condition ie

TAT	Count of Request ID
> 5 days <= 7 days	50
> 4 hours <=5 days	7
<= 4 hours	15

This is my expected output.

Many Thanks

Karthik

Re: DD:HH:MM format

tresB — Tue, 29 May 2018 06:48:05 GMT

LOAD REQUEST_NO,

Interval#(TAT,'DD:hh:mm') as TAT

Calculated dimension:

=If(TAT>5 and TAT<=7, '>5 days <=7 days',

if(TAT>4/24 and TAT<=5, '>4 hours days <=5 days', '<= 4 hours' ) )

Re: DD:HH:MM format

karthikoffi27se — Tue, 29 May 2018 06:48:48 GMT

stalwar1‌ Can you help me with this please

Re: DD:HH:MM format

Anonymous — Tue, 29 May 2018 07:30:23 GMT

Hello Karthik,

You have to separate days, hours and minutes and/or to get it easier calculate all in days:

LOAD

REQUEST_NO,

Sum(Day+Hour+Minute) as Days

Group By REQUEST_NO;

LOAD REQUEST_NO,

Subfield(TAT,':',1) as Day,

Subfield(TAT,':',2)/24 as Hour,

Subfield(TAT,':',3)/1440 as Minute

FROM TAT.xls (biff, embedded labels, table is Sheet1$);

Then you can use the function Class() to create your column Task or with a If Condition,

Hope this will help you,

Jonathan L.

Re: DD:HH:MM format

Anil_Babu_Samineni — Tue, 29 May 2018 07:53:54 GMT

Not really sure, why tresesco solution is not helpful for you? Can you elaborate more for that?