Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.
Yes thanks, but in my version the highest ID was also stored in the variable? stop only at runtime?
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.
not stop, still should be there
ok, tanks