topic Re: Split Start Date Time and End Date Time From Single Field in QlikView

Split Start Date Time and End Date Time From Single Field

dineshm030 — Thu, 23 Sep 2021 00:04:11 GMT

Hi All,

I need to split the Start Date Time and End Date Time with the Field value.

In the Excel, I want to add Start Date Time Field which is not equal to "0" in FAULT_NUMBER whereas End Date Time Field which is equal to "0" in FAULT_NUMBER.

Output should be like this below screenshot.

Kindly help me out. Attached the file for your ref. @MayilVahanan @sunny_talwar

Thanks in advance.

Regards,

Dinesh Kumar Mani.

Re: Split Start Date Time and End Date Time From Single Field

MayilVahanan — Thu, 23 Sep 2021 02:36:12 GMT

Might be, try like below

Sheet1:
LOAD Cell,
ScadaDate,
DateTimeEst,
FAULT_NUMBER,
If(FAULT_NUMBER <> 0, DateTimeEst) as StartTime,
If(FAULT_NUMBER = 0, DateTimeEst) as EndTime,
RecNo() as R1
FROM
[D:\Qlik\Com\Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

Temp:
Load Cell,ScadaDate, DateTimeEst, FAULT_NUMBER, StartTime, Alt(EndTime, Peek('DateTimeEst')) as EndTime1, EndTime, R1 Resident Sheet1 Order by DateTimeEst desc;

NoConcatenate

Final:
Load * Resident Temp Where (isnull(EndTime) or R1 = 1);

DROP Table Sheet1, Temp;

Drop field EndTime;

O/p:

Re: Split Start Date Time and End Date Time From Single Field

dineshm030 — Thu, 23 Sep 2021 06:38:45 GMT

Hi,

I achieved the given your script. And I need to split time diff by every half an hour. Attached the Excel file for your ref.

Kindly help me out.

Regards,

Dinesh Kumar Mani.

Re: Split Start Date Time and End Date Time From Single Field

sunny_talwar — Thu, 23 Sep 2021 17:27:11 GMT

Something like this

Sheet1: LOAD *, TimeStamp(Floor(StartTime, 1/48) + (IterNo()-1)/48) as AdjustStartTime, TimeStamp(Floor(StartTime, 1/48) + (IterNo())/48) as AdjustEndTime While Floor(StartTime, 1/48) + (IterNo()-1)/48 <= Ceil(EndTime1, 1/48); LOAD Cell, StartTime, EndTime1, TimeStamp(Floor(StartTime, 1/48)) as NewStartTime, TimeStamp(Ceil(EndTime1, 1/48)) as NewEndTime, [Time Diff (Mins)] FROM [C:\Users\talwars\Downloads\Test OP.xlsx] (ooxml, embedded labels, table is Sheet1);

Re: Split Start Date Time and End Date Time From Single Field

dineshm030 — Fri, 24 Sep 2021 10:03:26 GMT

Hi Sunny,

Start time is 05:29 and End time is 05:30. But Adjusttiming is showing 06:00 to 06:30.

Kindly help me out.

Thanks in advance.

Re: Split Start Date Time and End Date Time From Single Field

sunny_talwar — Fri, 24 Sep 2021 13:59:33 GMT

May be try with a slight change.. changed While statement to use < instead of <=

Sheet1: LOAD *, TimeStamp(Floor(StartTime, 1/48) + (IterNo()-1)/48) as AdjustStartTime, TimeStamp(Floor(StartTime, 1/48) + (IterNo())/48) as AdjustEndTime While Floor(StartTime, 1/48) + (IterNo()-1)/48 < Ceil(EndTime1, 1/48); LOAD Cell, StartTime, EndTime1, TimeStamp(Floor(StartTime, 1/48)) as NewStartTime, TimeStamp(Ceil(EndTime1, 1/48)) as NewEndTime, [Time Diff (Mins)] FROM [C:\Users\talwars\Downloads\Test OP.xlsx] (ooxml, embedded labels, table is Sheet1);

Re: Split Start Date Time and End Date Time From Single Field

dineshm030 — Fri, 24 Sep 2021 15:02:36 GMT

Thanks for your reply.

Still it showing same Sunny.

Re: Split Start Date Time and End Date Time From Single Field

dineshm030 — Fri, 24 Sep 2021 15:38:49 GMT

Below script i have used:

Sheet1:
LOAD Cell,
ScadaDate,
SFTagPath,
Timestamp(DateTimeEst) as DateTimeEst,
SCADA_FAULT_NUMBER,
Timestamp(If(SCADA_FAULT_NUMBER <> 0, DateTimeEst)) as StartTime,
Timestamp(If(SCADA_FAULT_NUMBER = 0, DateTimeEst)) as EndTime,
RecNo() as R1
FROM
[D:\Meritor\CONFIG\SCADA TABLE.xlsx]
(ooxml, embedded labels, table is Sheet1);

Temp:
Load Cell,ScadaDate, DateTimeEst, SCADA_FAULT_NUMBER, StartTime, Alt(EndTime, Peek('DateTimeEst')) as EndTime1, EndTime, R1 Resident Sheet1 Order by SFTagPath
,DateTimeEst desc;

NoConcatenate

Final:
Load * Resident Temp Where (isnull(EndTime) or R1 = 1);

DROP Table Sheet1, Temp;

Drop field EndTime;

Sheet1:
LOAD Distinct *,
TimeStamp(Floor(StartTime, 1/24) + (IterNo()-1)/24) as AdjustStartTime,
TimeStamp(Floor(StartTime, 1/24) + (IterNo())/24) as AdjustEndTime
While Floor(StartTime, 1/24) + (IterNo()-1)/24 < Ceil(EndTime1, 1/24);
LOAD *,
TimeStamp(Floor(StartTime, 1/24)) as NewStartTime,
TimeStamp(Ceil(EndTime1, 1/24)) as NewEndTime
Resident Final ;

DROP Table Final;

I have split hour by hour but i am getting duplicate values.

Any idea please @MayilVahanan @sunny_talwar