Split Start Date Time and End Date Time From Single Field

[dineshm030]

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.

[MayilVahanan]

HI 

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:

[dineshm030]

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.

[sunny_talwar]

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);

[dineshm030]

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.

[sunny_talwar]

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);

[dineshm030]

Thanks for your reply.

Still it showing same Sunny.

[dineshm030]

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