Save Data on Load &Store under Load Date

[bnelson111]

Is it possible to capture data expressions after a load? For example i have a order queue today of £45 and if i reload tomorrow it maybe £50 i would like a way to save the £45 under yesterday load date so i can see the trend of a moving total as once the work is done the value changes.

Sounds strange but its really a capture value at point of time? with a save to script or variable are a write to excel.

Thanks in advance. Brett

[sunny_talwar]

It seems like you can use incremental use where you would just add new data in and not replace the existing data. You can find a lot of discussions related to incremental load on the community.

HTH

Best,

S

[giakoum · Accepted solution]

you can save your orders daily in a qvd file, flagged with the date :

load

today() as identifier,

....

from....

and then load that qvd every day, add the new records, and save it overwriting the old qvd

What I am actually describing here is a typical incremental load

[Peter_Cammaert]

Shouldn't be too difficult to do. Let's say that at the end of your script, you create one or more rows with todays aggregated data. What data you want to keep is up to you and cannot be deduced from your OP, but you should at least include a column with today's date in this table. Call the table with aggregated data ... AggregatedData.

Next you do this:

:

IF Not IsNull(filetime('HistoricalData.qvd')) THEN

  CONCATENATE (AggregatedData)

  LOAD * FROM HistoricalData.QVD (qvd)

  WHERE DateStamp < today();

END IF

STORE AggregatedData INTO HistoricalData.QVD (qvd);

That's about it.

Peter

[Peter_Cammaert]

Keeps working, even if you run your Load script multiple times in a row. During development & testing for example.

[bnelson111]

can i save a variable into the incremental load? for example

Count(DISTINCT{$<[Logindate]-=''>}[UserCode]) which is variable $(vr22)

[bnelson111]

can i save a variable into the incremental load? for example

Count(DISTINCT{$<[Logindate]-=''>}[UserCode]) which is variable $(vr22)

[Peter_Cammaert]

You can save the contents of such a variable (after the variable is set), but not the result after evaluation. The reload engine doesn't understand set analysis. That's a UI expression-only feature.

You may have to work out some aggregation simulation in your script, that mimics what you do using expressions. It's all perfectly doable.