Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Vaku1606
Contributor III
Contributor III

Line capacity calculation

hello,

I have excel file with Line stop/maintenance defined. It looks like this:

LINE START_DATE END_DATE DESCRIPTION
L5000 4/7/2022 0:00 11/7/2022 0:00 Line stop 4.7.2022-10.7.2022
L5000 8/8/2022 0:00 15/08/2022 00:00:00 Line stop 8.8.2022-14.8.2022
L5000 22/12/2022 22:00:00 2/1/2023 0:00 Christmas shutdown 23.12.2022 - 1.1.2023
L7000 6/6/2022 0:00 13/06/2022 00:00:00 Maintenance 6.6.2022-12.6.2022
L7000 25/07/2022 00:00:00 1/8/2022 0:00 Line stop 25.7.2022-31.7.2022
L7000 22/12/2022 22:00:00 2/1/2023 0:00 Christmas shutdown 23.12.2022 - 1.1.2023
L8000 6/6/2022 0:00 13/06/2022 00:00:00 Line stop 6.6.2022-12.6.2022
L8000 25/07/2022 00:00:00 1/8/2022 0:00 Line stop 25.7.2022-31.7.2022
L8000 6/9/2022 0:00 7/9/2022 4:00 maintenance 6.9.2022
L8000 12/9/2022 10:00 15/09/2022 01:00:00 maintenance 12.9. - 14.9. 2022
L8000 22/12/2022 22:00:00 2/1/2023 0:00 Christmas shutdown 23.12.2022 - 1.1.2023
L9000 15/09/2022 01:00:00 16/09/2022 19:00:00 maintenance 15.9. - 16.9.2022
L9000 22/12/2022 22:00:00 2/1/2023 0:00 Christmas shutdown 23.12.2022 - 1.1.2023
M10000 15/08/2022 00:00:00 22/08/2022 00:00:00 Line stop 15.8.2022-21.8.2022
M10000 22/12/2022 22:00:00 2/1/2023 0:00 Christmas shutdown 23.12.2022 - 1.1.2023
M11000 11/7/2022 0:00 19/07/2022 00:00:00 Maintenance 11.7.2022 - 18.7.2022
M11000 22/12/2022 22:00:00 2/1/2023 0:00 Christmas shutdown 23.12.2022 - 1.1.2023

 

I made a script in Qlik which calculates the line capacity as follows: if the date is mentioned in the line stop report, than capacity is 0; otherwise is 24h.

My script is shown below:

LineData:
Load LINEline, Key, sum(LINESTOPPED) as LINESTOPPED group by LINEline, Key,linestartDateNum;
Load LINE as LINEline,LINE&Month(Date)&Year(Date) as Key,
num(monthstart(Date)) as linestartDateNum,
24 as LINESTOPPED;
LOAD LINE,

date(START+IterNo()-1) as Date

while (START+IterNo()-1) <END;
LOAD LINE,
makeDate(mid(START_DATE,7,4),mid(START_DATE,4,2),mid(START_DATE,1,2)) as START,
makeDate(mid(END_DATE,7,4),mid(END_DATE,4,2),mid(END_DATE,1,2)) as END,
DESCRIPTION

FROM [lib://ORTEMS/LINE_STOP_REPORT.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

 

It works fine, but still showing capacity for the previous months and for the current month showing the capacity of the full month.

Do you know how should I adjust the script, so that I get 0 capacity for the past months and for the current month only remaining capacity: example today is 5th of September and only 25 days left in September, so capacity in September should be calculated as (30-5)*24h if there is no stop/maintenance defined.

Thanks in advance and regards, 

Labels (3)
0 Replies