Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Replicate Above function in script

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:

1.PNG

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

2.PNG

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

Your help would very much appreciated,

Thank you,

Rawad

1 Solution

Accepted Solutions

Re: Replicate Above function in script

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 Replies

Re: Replicate Above function in script

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;

Not applicable

Re: Replicate Above function in script

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 :

3.PNG !

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

Thank you sunny, I do appreciate your help.

Re: Replicate Above function in script

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

Not applicable

Re: Replicate Above function in script

Works like a charm

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

Thanks a plenty ..

Re: Replicate Above function in script

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....

Community Browser