Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

linoybar
Contributor III

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

Re: Load max(ID) from QVD

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 ;

5 Replies

Re: Load max(ID) from QVD

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 ;

linoybar
Contributor III

Re: Load max(ID) from QVD

Amazing!

Thank you, Gysbert, that worked!

linoybar
Contributor III

Re: Load max(ID) from QVD

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
Contributor

Re: Load max(ID) from QVD

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;

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

mov
Esteemed Contributor III

Re: Load max(ID) from QVD

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