Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aveeeeeee7en
Not applicable

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

Tags (1)
5 Replies
tresesco
Not applicable

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

NetWorkDays() could be helpful here.

aveeeeeee7en
Not applicable

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

Hi Tresesco Sir could you help me on Script.

tresesco
Not applicable

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

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
Not applicable

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

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
Not applicable

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

Any idea of doing this???