Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
linoyel
Specialist
Specialist

Load max(ID) from QVD

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 )

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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 ;


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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 ;


talk is cheap, supply exceeds demand
linoyel
Specialist
Specialist
Author

Amazing!

Thank you, Gysbert, that worked!

linoyel
Specialist
Specialist
Author

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;

zahidrahim_ocp
Partner - Creator
Partner - Creator

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;

---------------------------------------------------------------------------------------------------------

Anonymous
Not applicable

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