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

[vishsaggi]

Try this?

Document_Header:

Load

*

Inline [

ID

0

1

2

3

4

5

6

];

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 [

ID

0

1

2

3

4

5

6

];

//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;

[trdandamudi]

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

[rubenmarin]

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

[Peter_Cammaert]

Do this step by step.

1. 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.
   
   
2. 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.