Converting Weeks, Hours into Days

[siva0606]

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

[Taoufiq_Zarra]

@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:

[RsQK]

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;