Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pushpita
Contributor

hour format conversion

Hi,

I want to convert duration field which is in "1days0hrs1min 13sec" format to [ <5mins, 5mins-30mins, 30 – 1hr,1hr-2hr,2hr-3hr,>3hr] format.

2 Replies

Re: hour format conversion

Hi,

Try this code.

Load *,if(TimeinMinutes < 5,'<5 Minutes',

  If(TimeinMinutes >= 5 and TimeinMinutes < 30,'5 - 30 Minutes',

  If(TimeinMinutes >= 30 and TimeinMinutes < 60,'5 - 1 Hour',

  If(TimeinMinutes >= 60 and TimeinMinutes < 120,'1 - 2 Hour',

  If(TimeinMinutes >= 120 and TimeinMinutes < 180,'2 - 3 Hour','> 3 Hour'))))) as Bucket;

LOad *,(DAYS * 24 * 60) + Hours + (Minutes / 60) + (Seconds / 3600) as TimeinMinutes;

Load XYZ,SubField(XYZ,'days',1) as DAYS,TextBetween(XYZ,'days','hrs') as Hours,TextBetween(XYZ,'hrs','min') as Minutes,TextBetween(XYZ,'min ','sec') as Seconds inline [

XYZ

1days0hrs1min 13sec

0days18hrs30min 10sec

];

Regards,

Kaushik Solanki

passionate
Valued Contributor

Re: hour format conversion

Hi Priyabrata,

You can even try using interval match.

PFA Solution.

Regards,

Pankaj

Community Browser