Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
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