topic Re: Preceding load and order by in App Development

Preceding load and order by

onmysi49 — Fri, 17 Jan 2020 08:30:36 GMT

Hello,

I have a question regarding preceding load in Qlik Sense.

I am developing an app where one of the goal is to assign each Job (ZWMSJOBSH.JOBID field) correct assigned time and finished time.

In order to achieve this I must break duration of each group of jobs (all jobs which fall in one group have the same "LFinTimeKey") into even fractions and assign each ZWMSJOBSH.JOBID "new" "Dif - Assigned time" and "Dif - Finished time". So the jobs follow one another in the group and Total duration of the group equals to "Finished time"-"Assigned time".

Basically I know how to achieve the desired result but I have encountered an interesting behaviour of preceding load in this case so I would like to know what is the cause for different results as shown below:

EXAMPLE 1 (incorrect result)

LastFinishedTime:
Load
ZWMSJOBSH.JOBID,
"Dif - Assigned time",
"Dif - Finished time";
Load *,
(("Job - Counter" - 1) * "Job - Duration") + "Assigned time" as "Dif - Assigned time",
(("Job - Counter") * "Job - Duration") + "Assigned time" as "Dif - Finished time";
Load
LFinTimeKey,
ZWMSJOBSH.JOBID,
"Assigned time",
"Finished time",
"Jobs count",
("Finished time"-"Assigned time")/"Jobs count" as "Job - Duration", If(Peek(LFinTimeKey) = LFinTimeKey, Rangesum(Peek("Job - Counter"),1) , 1) as "Job - Counter"
Resident LastFinishedTime_tmp
order by LFinTimeKey;

INCORRECT RESULT:
ZWMSJOBSH.JOBID Dif - Assigned time Dif - Finished time
0007493646 02.07.2019 00:10:57 02.07.2019 00:12:25
0007493647 02.07.2019 00:10:57 02.07.2019 00:12:25
0007493648 02.07.2019 00:10:57 02.07.2019 00:12:25

EXAMPLE 2 (correct result)

LastFinishedTime:
Load *,
(("Job - Counter" - 1) * "Job - Duration") + "Assigned time" as "Dif - Assigned time",
(("Job - Counter") * "Job - Duration") + "Assigned time" as "Dif - Finished time";
Load
LFinTimeKey,
ZWMSJOBSH.JOBID,
"Assigned time",
"Finished time",
"Jobs count",
("Finished time"-"Assigned time")/"Jobs count" as "Job - Duration",
If(Peek(LFinTimeKey) = LFinTimeKey, Rangesum(Peek("Job - Counter"),1) , 1) as "Job - Counter"
Resident LastFinishedTime_tmp
order by LFinTimeKey;

CORRECT RESULT:
ZWMSJOBSH.JOBID Dif - Assigned time Dif - Finished time
0007493646 02.07.2019 00:10:57 02.07.2019 00:12:25
0007493648 02.07.2019 00:12:25 02.07.2019 00:13:52
0007493647 02.07.2019 00:13:52 02.07.2019 00:15:20

The only difference is that in the first example there is one extra preceding load. I guess it has something to do with the different result of Order by when adding another preceding load. But I would like to understand what really happens.

Any help would be appreaciated. Thanks a lot.

Have a great day.

Re: Preceding load and order by

marcus_sommer — Fri, 17 Jan 2020 12:05:57 GMT

The issue is caused through the fact that not all relevant fields are included within your last preceding-load - especially the peek-field.

I suggest that you remained by the two-step preceding and then drop the unwanted fields with something like:

drop fields LFinTimeKey, "Job - Counter" from LastFinishedTime;

- Marcus

Re: Preceding load and order by

onmysi49 — Fri, 17 Jan 2020 12:26:59 GMT

Hello Marcus,

thanks a lot for a quick reply! That should solve it. I was also able to solve it with replacing this last preceding load with load resident. Your solution is better though.

I just would like to understand how it works "in the background" it means the peek is recalculated in the last preceding load and therefore the result is different from what I expected?

Thanks a lot. Have a good one

Re: Preceding load and order by

marcus_sommer — Fri, 17 Jan 2020 13:09:40 GMT

Within a preceeding-load it doesn't load the the specified table and applied on this the second/third load and so on - as would it be just another load-syntax for resident-loads - else the first record is taken and then passed through all preceeding-statements. If this is done it comes to the second record and so on.

If you now removed your keep-fields LFinTimeKey, "Job - Counter" from the last preceeding-step the next records couldn't access them anymore - they are gone. And of course this leads to the unwanted results.

- Marcus

Re: Preceding load and order by

onmysi49 — Fri, 17 Jan 2020 19:09:26 GMT

It is clear now. Well explained!

Many thanks!

Ondrej