topic Re: Daily Delta Field in QlikView

Daily Delta Field

qlikviewaf — Mon, 16 Jul 2018 10:19:11 GMT

Hi,

i build a script that generate a QVD, and everyday the QVD is refreshed adding a new line for the day with a Balance (field amount) for the specific day.

My goal is to build the Daily Delta, as difference between the Balance of the day and the Balance of the previous day,

How this can be achieved? Below is my original script.

Thank you for all the suggestion!

//LOAD TABEL WITH REVELANT DATA

WalkingImplantsTemporary:

Load

BusinessUnit,

Amount,

Today() as Date;

SQL SELECT *

FROM JDE90.PRODDTA.F4211 WHERE SDMCU=' EIT22' AND SDNXTR='560';

WalkingImplants:

LOAD

BusinessUnit,

Amount,

Date

RESIDENT WalkingImplantsTemporary

GROUP BY BusinessUnit,Date;

DROP TABLE WalkingImplantsTemporary;

////********** Load the previous QVD and perform the incremental load******************

Concatenate(WalkingImplants)

WalkingImplantsQVD:

LOAD

BusinessUnit,

Amount,

HistoricalWIEUR,

Date

FROM

[$(vSourceData)WalkingImplants.qvd]

(qvd)

Where

Date < Today();

Store WalkingImplants into $(vSourceData)WalkingImplants.qvd(QVD);

Re: Daily Delta Field

ujjwalraja — Mon, 16 Jul 2018 10:29:03 GMT

Add this to your load script.

Rangesum(Amount-Previous(Amount)) as DailyDelta

Re: Daily Delta Field

qlikviewaf — Mon, 16 Jul 2018 10:41:17 GMT

in which part of the script?

Re: Daily Delta Field

ujjwalraja — Mon, 16 Jul 2018 11:23:33 GMT

//LOAD TABEL WITH REVELANT DATA

WalkingImplantsTemporary:

Load

BusinessUnit,

Amount,

Today() as Date;

SQL SELECT *

FROM JDE90.PRODDTA.F4211 WHERE SDMCU=' EIT22' AND SDNXTR='560';

WalkingImplants:

Load *,

Rangesum(Amount-Previous(Amount)) as DailyDelta

;

LOAD

BusinessUnit,

Amount,

Date

RESIDENT WalkingImplantsTemporary

GROUP BY BusinessUnit,Date;

DROP TABLE WalkingImplantsTemporary;

////********** Load the previous QVD and perform the incremental load******************

Concatenate(WalkingImplants)

WalkingImplantsQVD:

LOAD

BusinessUnit,

Amount,

HistoricalWIEUR,

Date,

Rangesum(Amount-Previous(Amount)) as DailyDelta

FROM

[$(vSourceData)WalkingImplants.qvd]

(qvd)

Where

Date < Today();

Store WalkingImplants into $(vSourceData)WalkingImplants.qvd(QVD);