Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
E_Frederick
Contributor
Contributor

Using Previous() function with resident load

Hello all, 

I'm attempting to write a function that inserts a date ("End") based on a serial number recorded in the table. The function looks for a previous instance of SERIAL_NUMBER_RECEIVED and if none is found, populates the "End" field with Today's date. If a previous instance of SERIAL_NUMBER_RECEIVED is found, then "End" is populated with a date that is one day prior to the previous instance's Receive Date. 

In my dataset there is a single instance of a particular SERIAL_NUMBER_RECEIVED value, so I would expect to see the "End" field populated with today's date. However, when I look at the data in a straight table after the load, I see two values for "End". One populated with today's date and one populated with the date prior to the Recieve date. Can anyone explain what I'm doing wrong? 

 

Script Code: 

[ReturnsTemp]:
LOAD
ITEM_NUMBER,
CUSTOMER_NUMBER,
CUSTOMER_NAME,
ORDER_TYPE,
ORDER_NUMBER,
ORDERED_DATE,
DESCRIPTION,
LINE_TYPE,
LINE_STATUS,
RETURN_REASON_CODE,
SERIAL_NUMBER_RECEIVED,
ORDER_PACK_NOTES,
ORDER_SHIP_NOTES,
LINE_PACK_NOTES,
LINE_SHIP_NOTES,
LINE_NUMBER,
LINE_CATEGORY_CODE,
DATE_RECEIVED,
FROM [lib://SharedFolder_Quality_QVD/Returns_AVN_G1.QVD]
(qvd);

 

[Returns]:
LOAD
ITEM_NUMBER,
CUSTOMER_NUMBER,
CUSTOMER_NAME,
ORDER_TYPE,
ORDER_NUMBER,
ORDERED_DATE,
DESCRIPTION,
LINE_TYPE,
LINE_STATUS,
RETURN_REASON_CODE,
SERIAL_NUMBER_RECEIVED as Returned_SN,
ORDER_PACK_NOTES,
ORDER_SHIP_NOTES,
LINE_PACK_NOTES,
LINE_SHIP_NOTES,
LINE_NUMBER,
LINE_CATEGORY_CODE,
DATE_RECEIVED as [Receive Date],
Date(If(SERIAL_NUMBER_RECEIVED = Previous(SERIAL_NUMBER_RECEIVED), RangeSum(Previous(DATE_RECEIVED), -1), Today())) as End //CR Service Completion Date should be between start and end date
Resident ReturnsTemp
Order By SERIAL_NUMBER_RECEIVED, DATE_RECEIVED desc;

DROP Table ReturnsTemp;

Labels (3)
2 Replies
rwunderlich

The results seem correct to me.  You have two rows with serial "x".  The first "x" gets compared with the previous rows which is "y", different serial this row gets End value. 

Second "x" is compared with previous row, which is a match of "x" so it gets the Previous-1 result. 

-Rob

E_Frederick
Contributor
Contributor
Author

If I only load the first table, ReturnsTemp, there is only one value in the table for SN 15H005709, which is expected. when I load the second table, Returns, and drop ReturnsTemp, I end up with a duplicate.