Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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.
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
Works like a charm
Any idea as to why it needs to be 3 step process?
Thanks a plenty ..
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....