topic Re: Save Data on Load &Store under Load Date in QlikView

Save Data on Load &Store under Load Date

bnelson111 — Thu, 05 Mar 2015 13:08:22 GMT

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

Re: Save Data on Load &Store under Load Date

sunny_talwar — Thu, 05 Mar 2015 13:17:02 GMT

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,

Re: Save Data on Load &Store under Load Date

giakoum — Thu, 05 Mar 2015 13:18:28 GMT

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

Re: Save Data on Load &Store under Load Date

Peter_Cammaert — Thu, 05 Mar 2015 13:26:17 GMT

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

Re: Save Data on Load &Store under Load Date

Peter_Cammaert — Thu, 05 Mar 2015 13:27:52 GMT

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

Re: Save Data on Load &Store under Load Date

bnelson111 — Thu, 05 Mar 2015 14:32:01 GMT

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

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

Re: Save Data on Load &Store under Load Date

bnelson111 — Thu, 05 Mar 2015 14:34:36 GMT

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

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

Re: Save Data on Load &Store under Load Date

Peter_Cammaert — Thu, 05 Mar 2015 16:02:34 GMT

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.