topic Re: Store values even after data loads again in QlikView

Store values even after data loads again

Tue, 09 Feb 2016 21:08:44 GMT

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.

Re: Store values even after data loads again

Oleg_Troyansky — Tue, 09 Feb 2016 21:44:00 GMT

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;

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!

Re: Store values even after data loads again

Peter_Cammaert — Tue, 09 Feb 2016 21:50:49 GMT

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

Create an internal table with the current value and the current date
If a saved copy of this table exists (history), add all records from this copy that refer to other days
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

Re: Store values even after data loads again

Tue, 09 Feb 2016 21:54:56 GMT

The following error occurred:

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

Re: Store values even after data loads again

Tue, 09 Feb 2016 21:55:27 GMT

How to create an internal table?

Re: Store values even after data loads again

Peter_Cammaert — Tue, 09 Feb 2016 22:43:54 GMT

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

Re: Store values even after data loads again

Peter_Cammaert — Tue, 09 Feb 2016 22:46:46 GMT

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

Re: Store values even after data loads again

Wed, 10 Feb 2016 14:09:10 GMT

I tried this, Is this ok?

Re: Store values even after data loads again

petter — Wed, 10 Feb 2016 14:30:10 GMT

you have to change line 876 to this:

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

Re: Store values even after data loads again

Peter_Cammaert — Wed, 10 Feb 2016 14:35:17 GMT

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

Re: Store values even after data loads again

Wed, 10 Feb 2016 14:35:33 GMT

changed it. still showing error at autogenerate 1

Re: Store values even after data loads again

Wed, 10 Feb 2016 14:45:09 GMT

should qvd be a path?

Re: Store values even after data loads again

petter — Wed, 10 Feb 2016 15:09:01 GMT

At least you have a spelling error in the specification of file type:

(qbd)

should be

(qvd)

Re: Store values even after data loads again

Wed, 10 Feb 2016 15:12:07 GMT

Thank you,

corrected it. still giving same error.

Re: Store values even after data loads again

Wed, 10 Feb 2016 15:30:05 GMT

Re: Store values even after data loads again

petter — Wed, 10 Feb 2016 16:09:58 GMT

The problem is exactly what the error message states:

Instead of referring to a path like:

C:\Users.....

You must start the path like:

lib:// ....

Where the lib points to as to the file system is configured in Qlik Sense.

Re: Store values even after data loads again

Wed, 10 Feb 2016 16:32:18 GMT

tempdatestoretable:

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

RESIDENT ids;

CONCATENATE (tempdatestoretable)

LOAD *

FROM 'lib://libfolder/MyQVDTable.qvd' (qvd)

WHERE Not Exists(StoreDate);

STORE tempdatestoretable INTO 'lib://libfolder/MyQVDTable.qvd' (qvd);

DROP Table tempdatestoretable;

I have created a folder it is still giving invalid path.

Re: Store values even after data loads again

Wed, 10 Feb 2016 17:14:46 GMT

Should the folder already contain qvd file?

Re: Store values even after data loads again

Wed, 10 Feb 2016 17:24:52 GMT

Thank you for help. It worked