topic Re: incremental load in script in App Development

incremental load in script

boreus-rz — Thu, 03 May 2018 10:48:04 GMT

Hello dear community,
I would like to load my spreadsheets only incrementally. So only the records that have been added since the last loading.
For this, I have built the following sample conflict in the data load script.

LIB CONNECT TO 'host.de (user)';
[Table 1]:
LOAD
    [id] AS ID,
    [key_id] AS Key_id,
    [caseid] AS Case_id,
    [text] AS text;
SQL SELECT
    id,
    key_id,
    CaseID,
    text
FROM `Server`.`Table1`
WHERE id> '$ (vMaxID_Table1)';

[MaxiMalWertTab]:
Load max (Table1.id) AS MaxiMalWert resident Table1;
Let vMaxID_Table1 = Peek ('MaxiMalWert');
drop table MaxiMalWertTab;

CONCATENATE LOAD
[ID]
[KEY_ID]
[Case_d]
[Text]
FROM [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd)
where not Exists(ID)
;

store [table1] into [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd);

I built this script following the tutorial on help.qlik.com, but it does not seem to work. Can any of you please help me?
VG

Re: incremental load in script

Thu, 03 May 2018 11:41:05 GMT

This part should be above [Table1] load statement, and should reference the QVD as you're trying to get the newer data from SQL using the where clause based on the max id that is in the qvd:

[MaxiMalWertTab]:
Load max (Table1.id) AS MaxiMalWert resident Table1;
Let vMaxID_Table1 = Peek ('MaxiMalWert');
drop table MaxiMalWertTab;

Regards,

Nick

Re: incremental load in script

boreus-rz — Thu, 03 May 2018 14:03:19 GMT

what should it change?
Otherwise, I always determine at runtime, which was the last ID (the highest) and notice the variables in the variable.
What else does not happen with your idea? or?
VG

Re: incremental load in script

Thu, 03 May 2018 14:22:52 GMT

Your script should look something like this:

//-----------Start

// First check what the latest ID is from the data you already have

MaxValue:
Load

max (ID) AS MaxiMalWert

FROM [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd);

//Store the max ID value in a variable
Let vMaxID = Peek ('MaxiMalWert',0,'MaxiMalWertTab');

//Drop table that was needed to find the max ID value
drop table MaxValue;

//open connection to a server

LIB CONNECT TO 'host.de (user)';

//Fetch data from your database where the ID is greater than the value that is stored in vMaxID.

[Table_1]:
LOAD
    [id] AS [ID],
    [key_id] AS [KEY_ID],
    [CaseID] AS [Case_d],
    [text] AS [Text];
SQL SELECT
    id,
    key_id,
    CaseID,
    text
FROM `Server`.`Table1`
WHERE id> '$ (vMaxID)';

//Concat the old data from the QVD to the newly fetch data to create one table.
CONCATENATE

LOAD
     [ID]
     [KEY_ID]
     [Case_d]
     [Text]
FROM [lib: // resources (qlik1_administrator) /KPI/DEV/table1.qvd] (qvd);

//Store the table with the new data in a QVD so further use.
store table1 into [lib://resources (qlik1_administrator)/KPI/DEV/table1.qvd] (qvd);

//-----------End

you might want to check the column names for easy readability and understanding.

Hope the comments help with understanding what is going on.

Re: incremental load in script

boreus-rz — Fri, 04 May 2018 09:19:57 GMT

Yes thanks, but in my version the highest ID was also stored in the variable? stop only at runtime?

Re: incremental load in script

Fri, 04 May 2018 09:25:24 GMT

Correct, but at the wrong moment that the script runs. the variable was created/filled to late in the script.

What do you mean with "Stop only at runtime" ?

If your question was answered successfully, please do mark the correct answer that helped you.

Re: incremental load in script

boreus-rz — Fri, 04 May 2018 09:29:15 GMT

not stop, still should be there
ok, tanks