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,
