Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm doing an incremental load from QVD file and add data from database based on ID field.
The thing is that my QVD file contains 4 different parts of data - lets's say a, b, c,and d, and each part has it's own IDs.
So I have to find max(ID) for every part and then add new rows from database.
The problem is that finding max(id) from QVD file takes forever!
Please help me to improve the script. Thank you!
Here it is:
//Load data from QVD:
MyTable:
LOAD * FROM "$(vDataPath)\MyFile.qvd" (qvd);
//Find max(IDs): --> IT STUCKS HERE!!!
temp1:
LOAD max(ID) as MaxID1 Resident MyTable where Type='a';
temp2:
LOAD max(ID) as MaxID2 Resident MyTable where Type='b';
temp3:
LOAD max(ID) as MaxID3 Resident MyTable where Type='c';
temp4:
LOAD max(ID) as MaxID4 Resident MyTable where Type='d';
//Put max(IDs) in variables:
LET MaxID1= Peek('MaxID1 ',-1,'temp1');
LET MaxID2= Peek('MaxID2',-1,'temp2');
LET MaxID3 = Peek('MaxID3',-1,'temp3');
LET MaxID4 = Peek('MaxID4',-1,'temp4');
//Load new data from database:
Concatenate(MyTable)
LOAD *;
SQL SELECT *
FROM MyTableInDB
where Type='a'
and ID > $(MaxID1 )
Concatenate(MyTable)
LOAD *;
SQL SELECT *
FROM MyTableInDB
where Type='b'
and ID > $(MaxID2 )
Concatenate(MyTable)
LOAD *;
SQL SELECT *
FROM MyTableInDB
where Type='c'
and ID > $(MaxID3 )
Concatenate(MyTable)
LOAD *;
SQL SELECT *
FROM MyTableInDB
where Type='d'
and ID > $(MaxID4 )
You're not loading the max values from the qvd, but from a resident table.
Try this:
MyTable:
LOAD Type as MaxType, max(ID) as MaxID
FROM "$(vDataPath)\MyFile.qvd" (qvd)
GROUP BY Type;
LET MaxID1 = fieldvalue('MaxID',fieldindex('MaxType','a')) ;
LET MaxID2 = fieldvalue('MaxID',fieldindex('MaxType','b')) ;
LET MaxID3 = fieldvalue('MaxID',fieldindex('MaxType','c')) ;
LET MaxID4 = fieldvalue('MaxID',fieldindex('MaxType','d')) ;
DROP TABLE MyTable ;
You're not loading the max values from the qvd, but from a resident table.
Try this:
MyTable:
LOAD Type as MaxType, max(ID) as MaxID
FROM "$(vDataPath)\MyFile.qvd" (qvd)
GROUP BY Type;
LET MaxID1 = fieldvalue('MaxID',fieldindex('MaxType','a')) ;
LET MaxID2 = fieldvalue('MaxID',fieldindex('MaxType','b')) ;
LET MaxID3 = fieldvalue('MaxID',fieldindex('MaxType','c')) ;
LET MaxID4 = fieldvalue('MaxID',fieldindex('MaxType','d')) ;
DROP TABLE MyTable ;
Amazing!
Thank you, Gysbert, that worked!
There is another solution, a bit longer but works fast as well:
//Find max(ID) for Type='a':
TempTable:
LOAD ID as ID1 FROM "$(vDataPath)\MyFile.qvd" (qvd) where Type='a';
MaxID:
LOAD
Max(ID_) as MaxID1;
LOAD
FieldValue('ID1 ', IterNo()) as ID_
Autogenerate(1)
While not IsNull(FieldValue('ID1 ', IterNo()));
Let vMaxID1 = Peek('MaxID1', -1, 'MaxID');
DROP TABLES TempTable, MaxID;
Can you please examine my code i am getting below error:
Connector reply error: ErrorSource: OraOLEDB, ErrorMsg: ORA-00936: missing expression
On my below mentioned code after the line: /////////////////////////////////////////////// Load Incremental Data
---------------------------------------------------------------------------------------------------------
TempTable:
LOAD MAX(LAST_UPDATE_DATE) AS LUD
FROM [Lib://LIB_QVD/qlik_inc_load.QVD] (qvd);
LET MAX_LUD = FieldValue('LUD') ;
/////////////////////////////////////////////// Load Incremental Data
[QV_Table]:
SQL SELECT Id,
TrDate,
Name,
Amount ,
LAST_UPDATE_DATE
FROM apps.qlik_inc_load
WHERE last_update_date > $(MAX_LUD);
STORE QV_Table INTO [Lib://LIB_QVD/qlik_inc_load.QVD] (qvd);
DROP Table TempTable;
---------------------------------------------------------------------------------------------------------
Hi Zahid,
First of all, it is not a good idea to append your question to an existing discussion, especially if it is closed. You better open your own.
Second, the fieldvalue() requires two parameters, for example:
LET MAX_LUD = FieldValue('LUD',1) ;
I would rather use peek() here:
LET MAX_LUD = peek('LUD') ;
Third, you cannot use this syntax in Oracle:
WHERE last_update_date > $(MAX_LUD);
Take a look here, part 2:
Some nuances working with Oracle
In any case, open your own discussion, if you want somebody else to notice it.
Regards,
Michael