Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

boreus-rz
New Contributor

incremental load in script

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

1 Solution

Accepted Solutions
Not applicable

Re: incremental load in script

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

6 Replies
Not applicable

Re: incremental load in script

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

boreus-rz
New Contributor

Re: incremental load in script

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

Not applicable

Re: incremental load in script

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.

boreus-rz
New Contributor

Re: incremental load in script

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

Not applicable

Re: incremental load in script

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.

boreus-rz
New Contributor

Re: incremental load in script

not stop, still should be there
ok, tanks

Community Browser