Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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.
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);
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.
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);
Thanks for your reply.
Still it showing same Sunny.
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