Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Preceding load and order by

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:
ZWMSJOBSH.JOBID,
"Dif - Assigned time",
"Dif - Finished time";
(("Job - Counter" - 1) * "Job - Duration") + "Assigned time" as "Dif - Assigned time",
(("Job - Counter") * "Job - Duration") + "Assigned time" as "Dif - Finished time";
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:
(("Job - Counter" - 1) * "Job - Duration") + "Assigned time" as "Dif - Assigned time",
(("Job - Counter") * "Job - Duration") + "Assigned time" as "Dif - Finished time";
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.

4 Replies

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

Contributor III
Author

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

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

Contributor III
Author

It is clear now. Well explained!

Many thanks!

Ondrej