Store values even after data loads again

[#1]

Hi,

I have created an application with REST connection. the data is scheduled to load daily. I want to store the count of a variable in a table having one row as date field and other as count against each field. Even if data loads this table should not be lost. how to do that?

I have taken the count of the field in a variable let vStart = followers; I want to go on putting this in a table on daily basis. And save this table even if the data loads everyday.

[Oleg_Troyansky]

You can save the variable in a table like this:

SavedVariables:

LOAD

     $(vStart) as Start

...

AUTOGENERATE 1

;

Then, you can store this table on the hard drive using the STORE command:

STORE SavedVariables INTO MyFile.QVD;

or

STORE SavedVariables INTO MyFile.CSV (TXT);

If you really want to preserve the files for each reload, concatenate the timestamp into the file name.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

[Peter_Cammaert]

You could use a three-step incremental load in your script like this:

1. Create an internal table with the current value and the current date
2. If a saved copy of this table exists (history), add all records from this copy that refer to other days
3. Store the resulting table in the external copy (QVD)

This way, you make sure that:

• Only one record is written for every day, even if you run the script twice in a day
• If your history file doesn't exist yet, it gets created during the first reload.

Best,

Peter

[#4]

The following error occurred:

Syntax error, missing/misplaced FROM: follstoretemmptable: LOAD as storevar AUTOGENERATE 1

[#5]

How to create an internal table?

[Peter_Cammaert]

In Oleg's example, SavedVariables is an internal table.

[Peter_Cammaert]

Your variable is empty and as a result expands to nothingness, leading to an invalid LOAD statement.

LOAD as storevar AUTOGENERATE 1 is lacking a value (after the LOAD keyword) to put in storevar

[#8]

I tried this, Is this ok?

[petter]

you have to change line 876 to this:

let vStart = Peek( 'followers' , 0 , 'tempdatestoretable' );

[Peter_Cammaert · Accepted solution]

My 2cts:

let vStart = followers;

You cannot do this in QlikView script, because followers may be a field with many values. They won't fit in a single variable.

Use the peek() function to look in a cell of a specific row of a specific table. For example:

let vStart = peek('followers'); // Simplified call

will look in column followers of the first row of the table that was created last. See Petter's post for the full call format.

However the simplest approach is to load count([Values]) together with the current date and store that one in a QVD immediately. No intermediate table processing. Your script then becomes:

:

tempdatestoretable:

LOAD Count(values) AS Followers, Floor(Today()) as StoreDate

RESIDENT ids;

CONCATENATE (tempdatestoretable)

LOAD *

FROM MyQVDTable.qvd (qvd)

WHERE Not Exists(StoreDate); // Don't load rows that already exist

STORE tempdatestoretable INTO MyQVDTable.qvd (qvd);

DROP Table tempdatestoretable;

:

Now, you still have to add a check to see whether the QVD really exists. The first time you run this code, there won't be a QVD...

Best,

Peter