topic Re: Replicate Above function in script in App Development

Replicate Above function in script

Tue, 31 Jan 2017 09:17:05 GMT

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

Re: Replicate Above function in script

sunny_talwar — Tue, 31 Jan 2017 10:32:00 GMT

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;

Re: Replicate Above function in script

Tue, 31 Jan 2017 10:53:34 GMT

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.

Re: Replicate Above function in script

sunny_talwar — Tue, 31 Jan 2017 12:00:09 GMT

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

Re: Replicate Above function in script

Tue, 31 Jan 2017 12:41:56 GMT

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

sunny_talwar — Tue, 31 Jan 2017 12:49:09 GMT

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