topic Re: Converting Weeks, Hours into Days in App Development

Converting Weeks, Hours into Days

siva0606 — Tue, 30 Nov 2021 16:25:32 GMT

Hi All,

I have the below Timeline column in my script and need to calculate the Output (Days) column.

Timeline column only has Weeks, days and hrs and below table is the example.

Here, 1w = 5 days.

I have to convert all the Weeks days, hours to Days. Kindly help me on this calculation.

Timeline	Output (Days)
2w 2d	12
2 d	2
4 d	4
1w 3d	8
1 d	1
1 w	5
2w 4d	14
3 w	15
3 d	3
1w 2d	7
2w 3d	13
2w 1d	11
1h	0.0416667
4h	0.166667
7h	0.291667
3h	0.125

Best Regards,

Siva

Re: Converting Weeks, Hours into Days

Taoufiq_Zarra — Tue, 10 Aug 2021 10:20:43 GMT

@siva0606 maye be this solution :

load Timeline,evaluate(if(match(right(outputtmp,1),'+')>0,outputtmp&'0',outputtmp)) as output; load Timeline,replace(replace(replace(replace(Timeline,' ',''),'w','*5+'),'d','*1+'),'h','*0.0416667') as outputtmp; LOAD * INLINE [ Timeline 2w 2d 2 d 4 d 1w 3d 1 d 1 w 2w 4d 3 w 3 d 1w 2d 2w 3d 2w 1d 1h 4h 7h 3h ];

output:

Re: Converting Weeks, Hours into Days

RsQK — Tue, 10 Aug 2021 10:21:29 GMT

Hey, try this:

LET vHourVal = 1/24; LET vDayVal = 1; LET vWeekVal = 5; temp: LOAD *, ROWNO() AS row_id; LOAD * INLINE [ Timeline 2w 2d 2 d 4 d 1w 3d 1 d 1 w 2w 4d 3 w 3 d 1w 2d 2w 3d 2w 1d 1h 4h 7h 3h ]; temp_values: LOAD *, IF(LEN(temp_time_val)=1,PURGECHAR(tl,CHR(32)),temp_time_val) as time_val; LOAD row_id, Timeline as tl, SUBFIELD(Timeline,CHR(32)) AS temp_time_val RESIDENT temp; DROP FIELDS tl,temp_time_val; temp_values2: NoConcatenate LOAD DISTINCT * RESIDENT temp_values; DROP TABLE temp_values; temp_values3: LOAD *, ROWNO() AS row_id_2; LOAD *, days_amt*unit_q as result; LOAD *, PICK(MATCH(unit_type,'h','d','w'),'$(vHourVal)','$(vDayVal)','$(vWeekVal)') as days_amt; LOAD *, LOWER(RIGHT(time_val,1)) AS unit_type, LEFT(time_val,LEN(time_val)-1) AS unit_q RESIDENT temp_values2; DROP TABLE temp_values2; LEFT JOIN (temp) LOAD row_id, SUM(result) AS days RESIDENT temp_values3 GROUP BY row_id; DROP TABLE temp_values3;