Replicate Above function in script

[#1]

Dear Community,

I am trying to read the previous Record at any given record and trying to do so in the script.

Now, when I use the the above function in a chart visualization it works fine. But when I try using the Peek() or Previous() functions in the script I get duplicate records.

Desired Result:

What I get using Peek() or Previous() functions with an order by clause:

How do I get my desired output using Peek() or Previous()?

Your help would very much appreciated,

Thank you,

Rawad

[sunny_talwar]

You might be having more than one instance of OrderID in your application, right? Chart is looking at Distinct values of the combination of your dimension/s, whereas the script looks at each and every record unless you tell it otherwise. To get make this work in the script, you will have to take a resident load of distinct OrderID to to do....

NewTable:

LOAD DISTINCT OrderID,

          Previous(OrderID) as Test2

Resident ....

Order By OrderID;

[#3]

Yes that is correct, I do have more than one OrderID intance. I have already tried creating a resident load and I just tried it again unsuccessfully.

I tried this :

!

I'll attach the excel file maybe you can provide me some insight.

Thank you sunny, I do appreciate your help.

[sunny_talwar · Accepted solution]

I guess it needs to be a three step process

Orders:

LOAD OrderID,

     OrderDate,

     Month,

     Year,

     CustomerID,

     EmployeeID,

     ShipperID,

     ProductID,

     Sales,

     Costs,

     Profit,

     Quantity,

     Discount,

     Freight

FROM

[..\..\Downloads\QlikSenseWorkshop - 2.0.xlsx]

(ooxml, embedded labels, table is Orders);

NewTable:

LOAD Distinct OrderID

Resident Orders;

Order2:

LOAD *,

  Previous(OrderID) as OrderCode

Resident NewTable;

DROP Table NewTable;

Tested the script in QlikView, but don't see any issue why it shouldn't work with Qlik Sense.

Best,

Sunny

[#5]

Works like a charm

Any idea as to why it needs to be 3 step process?

Thanks a plenty ..

[sunny_talwar]

To tell you the truth, I have no idea... I would have hoped that it would work in 2 steps... but I guess I need three. Even the preceding load did not work....