Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aveeeeeee7en
Specialist III
Specialist III

Calculate Time Interval on the Basis of Logic b/w 2 Timestamps

Hi Community

I am referring below thread to fulfill my requirement:

Re: Calculate hours between two Date/Time strings

But got stuck inbetween my Logic.

Requirement:

1) I have an Excel which has Start Time & End Time against Part Number. So, for these cases I have to  calculateTime Interval on the basis of their defined Window. For some Part Numbers, it will work from Mon-sat or Mon-Fri or All 7 Days  as per the defined window.

Also, need to exclude Holidays.

2) Those cases, which are not in excel calculate their Time Interval on the basis of Flags:

If Flag is CDE than consider Mon-Sat - 9:30 - 18:00

If Flag is AB than consider Mon-Fri - 9:00 - 17:30

Logic:

Logic.png

Desired Output:

Desired Output.png

Also,see the Attachments.

Regards

Av7eN

5 Replies
tresesco
MVP
MVP

NetWorkDays() could be helpful here.

aveeeeeee7en
Specialist III
Specialist III
Author

Hi Tresesco Sir could you help me on Script.

tresesco
MVP
MVP

I don't find date in your input data. Your desired output excel sheet is not linked to windowmain sheet too. Only the logic(based on flag) of sat/sun exclusion is clear to me. Try to logically link the sheets and explain, and may be a clue on how you get the date could help a bit.

aveeeeeee7en
Specialist III
Specialist III
Author

Here, Number is the Primary Key between 2 tables WindowMain & Desired Output:

Table1:
LOAD Number,
     CompanyName,
     [Part Number],
     Flag,
     Mon,
     Tue,
     Wed,
     Thu,
     Fri,
     Sat,
     Sun
FROM
[Window and Output.xlsx]
(ooxml, embedded labels, table is WindowMain);

LEFT JOIN

LOAD Number,
     CreationTime,
     OutTime
FROM
[Window and Output.xlsx]
(ooxml, embedded labels, table is [Desired Output]);


Main_Table:
LOAD *,
'' AS Junk
Resident Table1;
Drop Table Table1;

Also, See this:

Logic123.png

aveeeeeee7en
Specialist III
Specialist III
Author

Any idea of doing this???