fill previous empty cell with the previous loaded record

[1600eads]

Hello, I am having an issue due to the way the data is structured in our database.

Based on the image above - a salesperson will manually update the FRUIT dimension which will append the next row into the database. In this case, I'd like to subtract the ENTERED_TIME and SOLD_TIME to get the difference (although this will yield 0). Qlik doesn't return any value/answer due to the offset of rows. Is there a way flattened this out by possibly creating another column to separate the FRUIT dimension to have the rows in one line? Is there another way to achieve this so that I can subtract the two dates? Thank you.

[Daniel_Pilla]

Hi @1600eads ,

Could you provide more sample data with additional customer IDs and how you'd like it to look? There are many ways of modeling and transforming data, however I'm not sure I fully understand what you'd like to see enough to tell you how to do it.

Cheers,

[1600eads]

Thank Daniel. 

A bit background info- I will have, at max, 2 rows for a customerID. They are flagged initially as RV_RDY, and RVC_DONE when they complete their transaction. Both of these values will have a corresponding timestamp and what I am trying to achieve is the difference of the two timestamps from when it was RV_RDY and RVC_DONE. Because they are grouped into a single dimension for both the dimension - flag_indicator and time_stamp_completed I am running into issues trying to find the difference of the 2 dates. Attached my sample data.

[Daniel_Pilla · Accepted solution]

Try this:

Map_RCV_Completed:
MAPPING LOAD
    customerID,
    time_stamp_completed AS RVC_DONE_time_stamp_completed
FROM [lib://DataFiles/test_flag.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE flag_indicicator = 'RVC_DONE';

Data:
LOAD
	*,
    Interval(RVC_DONE_time_stamp_completed - RV_RDY_time_stamp_completed,'D') AS expected_answer
    ;
LOAD
    customerID,
    Date(ApplyMap('Map_RCV_Completed',customerID,Null())) AS RVC_DONE_time_stamp_completed,
    time_stamp_completed AS RV_RDY_time_stamp_completed
FROM [lib://DataFiles/test_flag.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE flag_indicicator = 'RV_RDY';