Preceding load and order by

[onmysi49]

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.

[marcus_sommer]

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

[onmysi49]

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

[marcus_sommer]

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

[onmysi49]

It is clear now. Well explained!

Many thanks!

Ondrej