How to get Previous Modified date from the QVD

[lakshman1031]

Hi All,

I have date field like OrderDate every day we are refreshing the data and OrderDate will change.

Now we want to see the last modified oderDate.

For Example:

Before Reload the QVD

OrderDate:

30-09-2024

1-10-2024

2-10-2024

Now I refreshed Data then

after Refreshing Data

OrderDate:

1-10-2024

2-10-2024

3-10-2024

Now I want to see one more column like Previous OrderDate, in that i want to see the last modified date.

I want to see the data like below way.

NewOrderDate, PreviousOrderDate

1-10-2024,    30-10-2024

2-10-2024,    01-10-2024 

3-10-2024,    02-10-2024

Can you please suggest me how to achieve this scenario.

Thanks & Regards,

Lakshman

[marksouzacosta]

Hi @lakshman1031, do you have a Unique ID field? If so, the logic will be something like this:

// Load your data from your data source. I'll call it TempOrder table
TempOrder:
LOAD 
OrderId, 
OrderNo, 
Date#(OrderDate,'D-M-YYYY') AS OrderDate // Just to make sure I have a valid date field
INLINE [
OrderId, OrderNo, OrderDate
1, Order1, 30-09-2024
2, Order2, 1-10-2024
3, Order3, 2-10-2024
];


// Load your historical Order.qvd only with the OrderID (the unique field) and the OrderDate renamed as PreviousOrderDate. Left join it to TempOrder
If Not IsNull(QvdCreateTime('lib://DataFiles/Order.qvd')) Then

	LEFT JOIN(TempOrder)
    LOAD
      OrderId,
      Date(OrderDate,'D-M-YYYY') AS PreviousOrderDate
    FROM
        [lib://DataFiles/Order.qvd](qvd)
    ;

// If the Order.qvd does not exist, just add the PreviousOrderDate to all your rows.
Else

	LEFT JOIN(TempOrder)
    LOAD
      Date(Null(),'D-M-YYYY') AS PreviousOrderDate
      AutoGenerate(1)
    ;

End If


// Get the correct PreviousOrderDate
NoConcatenate

Order:
LOAD
    OrderId,
    OrderNo,
    OrderDate,
    Date(Alt(PreviousOrderDate, OrderDate),'D-M-YYYY') AS PreviousOrderDate
RESIDENT
	TempOrder
;

DROP TABLE TempOrder;

STORE Order INTO [lib://DataFiles/Order.qvd](qvd);

DROP TABLE Order;

Regards,

Mark Costa