DAILY HISTORY

[ba11]

Hello all,

I have this table in my script

ORDER_CHANGES:
LOAD
DATE,
JoinCUST_ORDER_LINE_ID,
IF(STATUS = 'R',1,-1) * IF(DATE = FLOOR($(vCHANGEDT)),AMOUNT_OUTSTANDING,0) AS ORDER_CHANGES.DAILY_MOVEMENT,
IF(STATUS = 'R',1,-1) * IF(DATE < FLOOR($(vCHANGEDT)) AND DATE >= WEEKSTART($(vCHANGEDT)),AMOUNT_OUTSTANDING,0) AS ORDER_CHANGES.WEEKLY_MOVEMENT,
IF(STATUS = 'R',1,-1) * IF(DATE < WEEKSTART($(vCHANGEDT)),AMOUNT_OUTSTANDING,0) AS ORDER_CHANGES.PERIOD_MOVEMENT
RESIDENT TEMP_ORDER_STATUS_CHANGE;

vCHANGEDT = NUM(IF(WEEKDAY(TODAY())='Mon', DAYEND(TODAY()-3),DAYEND(TODAY()-1)))

At the moment the ORDER_CHANGES.DAILY_MOVEMENT only returns values of daily order changes from the previous day, the values are not stored nor accumulated. What I want to do is store the daily movement values in a QVD and accumulate it overtime so I can display it on a trend graph and be able to select by month and year.

I have the general idea of how to go about it but still need guidance, thank you.

[Anil_Babu_Samineni]

@ba11 Perhaps this way?

If(IF(STATUS = 'R',1,-1) * IF(DATE = FLOOR($(vCHANGEDT)),AMOUNT_OUTSTANDING,0) = Previous(IF(STATUS = 'R',1,-1) * IF(DATE = FLOOR($(vCHANGEDT)),AMOUNT_OUTSTANDING,0)),

RangeSum(Peek(ORDER_CHANGES.DAILY_MOVEMENT), STATUS),

STATUS) as ORDER_CHANGES.DAILY_MOVEMENT,

[ba11 · Accepted solution]

What I did was store the table in a QVD, and then used concatenate to keep appending unto the table;

//After loading DAILY_HISTORY table

if Alt(FileSize('lib://Data:DataFiles/TestVisualERP_DAILY_HISTORY.qvd'), 0) > 0 THEN

CONCATENATE(DAILY_HISTORY)
LOAD
*
FROM [lib://Data:DataFiles/TestVisualERP_DAILY_HISTORY.qvd](qvd)
;
END IF

**********************************

Can anyone please help with how I can create a backup of the QVD file in case this gets corrupt? At the moment I add another STORE statement after the first one as backup, but I realized whatever happens to the first one will affect the second because every time it loads it overwrites what's already in the file.

Any other way to create a backup that is safe?