topic Re: load script does not save the variable in QlikView

load script does not save the variable

rein_nigul — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I would like to find the highest possible ID in the table of the incremental load script in the document table

In the Document_Header table, I see the largest ID but can not be saved as a variable.

vMaxDokID is empty

What's wrong with me?

Document_Header:

SQL SELECT

max(ID)

FROM "DOCUMENT_HEADER"

WHERE DOCUMENTDATE <=$(alguskpv_SQL)

;

LET vMaxDokID= Peek('MaxDokID',0, 'MinMax');

Re: load script does not save the variable

its_anandrjs — Fri, 15 Sep 2017 10:18:42 GMT

Did you check your table Document_Header gives max number also you not use Group By in the table or not once check this or you can try with the resident load.

Document_Header:

SQL SELECT

FROM "DOCUMENT_HEADER";

Store Document_Header into Document_Header.qvd;

Drop Table Document_Header;

MaxTable:

Load

max(ID) as MaxID

FROM [DOCUMENT_HEADER]

WHERE DOCUMENTDATE <= $(alguskpv_SQL)

Group BY ID;

LET vMaxDokID= Peek('MaxTable',0, 'MaxID');

Re: load script does not save the variable

its_anandrjs — Fri, 15 Sep 2017 10:20:07 GMT

Final Script

Document_Header:

SQL SELECT *,

FROM "DOCUMENT_HEADER";

Store Document_Header into Document_Header.qvd;

Drop Table Document_Header;

MaxTable:

Load

max(ID) as MaxID

FROM Document_Header.qvd

WHERE DOCUMENTDATE <= $(alguskpv_SQL)

Group BY ID;

LET vMaxDokID = Peek('MaxTable',0, 'MaxID');

Re: load script does not save the variable

vinayakg — Fri, 15 Sep 2017 10:45:02 GMT

Try This One:

Document_Header:

SQL SELECT

max(ID) as MaxID

FROM "DOCUMENT_HEADER"

WHERE DOCUMENTDATE <=$(alguskpv_SQL);

;

LET vMaxDokID= Peek('MaxID',0, 'Document_Header');

Re: load script does not save the variable

Peter_Cammaert — Fri, 15 Sep 2017 12:39:19 GMT

Are you sure that the use of $(alguskpv_SQL) in your SQL SELECT WHERE clause isn't in need of some quoting? AFAIK some DBMS don't like naked date specifications as they often look too much like ordinary calculations and will be treated as such...

Check the document log and have a look at what this WHERE clause expands to. The log file will contain all statement formats after $-sign expansion, which is quite handy.

Re: load script does not save the variable

Kushal_Chawda — Fri, 15 Sep 2017 13:20:31 GMT

what is the value in variable $(alguskpv_SQL) ? which is the underlying databse, oracle or SQL server?

Re: load script does not save the variable

florentina_doga — Fri, 15 Sep 2017 13:42:03 GMT

MaxTable:

Load

max(ID) as MaxID

FROM Document_Header.qvd

WHERE DOCUMENTDATE <= $(alguskpv_SQL);

LET vMaxDokID = Peek('MaxID',0, 'MaxTable');

Re: load script does not save the variable

florentina_doga — Fri, 15 Sep 2017 13:45:46 GMT

syntax peek is

peek(fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label, see Table Labels, without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Re: load script does not save the variable

vishsaggi — Fri, 15 Sep 2017 13:53:20 GMT

Are you trying to load the MaxID from this table?-> Document_Header if yes, you have to include the table name in the peek like:

Document_Header:

SQL SELECT

max(ID) AS MaxDokID

FROM "DOCUMENT_HEADER"

WHERE DOCUMENTDATE <= '$(alguskpv_SQL)'

;

LET vMaxDokID= Peek('MaxDokID',0, 'Document_Header');

Re: load script does not save the variable

rein_nigul — Fri, 15 Sep 2017 17:01:52 GMT

Unable to save the highest value of the vMaxDokID to the variable

The vMaxDokID variable is empty, which is wrong?

I use the following Load script;

Document_Header:

Load

Inline [

];

Document_ID:

Load

max(ID) as MaxID

Resident Document_Header;

LET vMaxDokID = Peek('MaxID', 0 , 'Document_Header');

Drop Table Document_Header;

Re: load script does not save the variable

vishsaggi — Fri, 15 Sep 2017 18:01:08 GMT

Try this?

Document_Header:

Load

Inline [

];

LEFT JOIN (Document_Header)

Document_ID:

Load

max(ID) as MaxID

Resident Document_Header;

LET vMaxDokID = Peek('MaxID', 0 , 'Document_Header');

Drop Table Document_Header;

IF you do not use the JOIN it will create two tables 1, Document_Header and 2, Document_ID. So when the variable is reading from Document_Header there is no field MaxID hence no data in it. You can also try like:

Document_Header:

Load

Inline [

];

//LEFT JOIN (Document_Header)

Document_ID:

Load

max(ID) as MaxID

Resident Document_Header;

LET vMaxID = Peek('MaxID', 0 , 'Document_ID');

Drop Table Document_Header, Document_ID;

Re: load script does not save the variable

trdandamudi — Fri, 15 Sep 2017 18:20:48 GMT

The below code will retain the maxid in the variable vMaxDokID. Hope this helps.

Document_Header:
Load
Max(ID) as MaxID;
Load
*
Inline [
ID
0
1
2
3
4
5
6
];

LET vMaxDokID = Peek('MaxID');

Re: load script does not save the variable

rubenmarin — Fri, 15 Sep 2017 18:29:46 GMT

Hi Rein, The first parameter of Peek is the field name, second the index (starting at zero), and 3rd the table name, so, if you don't assign an alias to max(ID) the field will be called 'max(ID)' -or 'MAX(ID)'?- and the table is 'Document_Header', so based on the script in the first post:

Document_Header:

SQL SELECT

max(ID)

FROM "DOCUMENT_HEADER"

WHERE DOCUMENTDATE <=$(alguskpv_SQL)

;

LET vMaxDokID= Peek('max(ID)',0, 'Document_Header');

Re: load script does not save the variable

Peter_Cammaert — Fri, 15 Sep 2017 20:01:55 GMT

Do this step by step.

Does the SQL SELECT generate a table with a Max value (use the Table Viewer to check this by pressing Ctrl-T)? If so, note the table name, and the field name. If not, check whether the ID field contains numerical data. Max() won't work on string representations. In the latter case it will generate a table with a single record containing a single NULL value.
How to check that the ID values are OK? Put the ID field in a listbox on a sheet. All values should be right-aligned.
If the table really contains your MaxID value and the value is correct, go to the next statement with the Peek() call. Follow the instructions provided by Ruben. You can omit the third parameter from the call (the table name) if the field name only exists in the table that contains your Max value. The LET statement becomes pretty simple, but you have to correctly specify the field name as present in the table. Use the Table Viewer (Ctrl-T) to check the name.

It's fairly easy to debug this, if you do it step-by-step.