load script does not save the variable

[rein_nigul]

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');

[its_anandrjs · Accepted solution]

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

*,

ID

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');

[its_anandrjs]

Final Script

Document_Header:

SQL SELECT *,

ID

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');

[vinayakg]

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');

[Peter_Cammaert]

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.

[Kushal_Chawda]

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

[florentina_doga]

MaxTable:

Load

max(ID)  as MaxID

FROM Document_Header.qvd

WHERE  DOCUMENTDATE <= $(alguskpv_SQL);

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

[florentina_doga]

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.

[vishsaggi]

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');

[rein_nigul]

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 [

ID

0

1

2

3

4

5

6

];

Document_ID:

Load

max(ID) as MaxID

Resident Document_Header;

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

Drop Table Document_Header;