Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
dineshm030
Creator II
Creator II

Split Start Date Time and End Date Time From Single Field

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.

dineshm030_0-1632354653056.png

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

Thanks in advance.

Regards,

Dinesh Kumar Mani.

Labels (3)
7 Replies
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:

MayilVahanan_0-1632364533085.png

 

Thanks & Regards,
Mayil Vahanan R
dineshm030
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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.

dineshm030_0-1632477668920.png

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
Creator II
Creator II
Author

Thanks for your reply.

Still it showing same Sunny.

dineshm030_0-1632495468741.png

 

dineshm030
Creator II
Creator II
Author

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.

dineshm030_0-1632497899504.png

Any idea please @MayilVahanan  @sunny_talwar