Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello experts,
Please can anyone help on this.
I have a duration field which is containing the data like below.
Ex:
00:02:55
1d 01:25:22
4d 00:06:55
For these above records i need convert in to decimal values and multiply with 24.
For 1d 01:25:22 and 4d -> i know that we need to split and multiply by 24 then we need to add both the values.
how to convert and multiply with duration and main challenging with 1d and 4d.
Attached the excel for reference.
Try this?
Decimal:
LOAD *, IF(IsNum(Duration), Duration*24, Num((Left(StrDuration,1)*24)+ (Subfield(StrDuration, ' ',2)*24))) AS FinalDurationInt;
//LOAD *, Subfield(StrDuration, ' ',2) AS StrDurationTime;
LOAD Sales,
IF(NOT ISNUM(Duration), Interval#(Replace(Duration, 'd',''), 'd h:mm:ss')) AS StrDuration,
Replace(Duration, 'd','') As Duration,
Required
FROM
[New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);
Try this?
Decimal:
LOAD *, IF(IsNum(Duration), Duration*24, Num((Left(StrDuration,1)*24)+ (Subfield(StrDuration, ' ',2)*24))) AS FinalDurationInt;
//LOAD *, Subfield(StrDuration, ' ',2) AS StrDurationTime;
LOAD Sales,
IF(NOT ISNUM(Duration), Interval#(Replace(Duration, 'd',''), 'd h:mm:ss')) AS StrDuration,
Replace(Duration, 'd','') As Duration,
Required
FROM
[New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);
Thanks vishwarath.
You are give a good suggestion to me,its working.
Happy to hear. Please close the thread by marking Correct Answer.