Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
elijahabel
Contributor III
Contributor III

Where Not Exists Loading only one row

Our team needs to implement incremental loading, and our loading is currently done in batches. The idea is that each group of rows from one batch is given a load_id, and we should be able to use that to load only new rows in.

[Table1]:

Load Value,

          Department,

          load_id as LoadID

          from [lib://....qvd](qvd);

Concatenate(Table1)

Load Value,

          Department,

          load_id as LoadID

          from IMPALA.Database

          where not exists(LoadID, load_id);

This issue is that this only loads the first row of each new load_id and then it stops. Seems that after the first row is loaded, it no longer passes the "not exists" because, well, it exists now.

Are there any other methods for loading using an ID like this that is not unique to each row, but still unique to each load?

Thank you for any help.

1 Solution

Accepted Solutions
elijahabel
Contributor III
Contributor III
Author

An Pham wrote:

Hi

Please try this

  1. [Table1]: 
  2. Load Value, 
  3.           Department, 
  4.           load_id as LoadID 
  5.           from [lib://....qvd](qvd); 
  6.  
  7. noConcatenate 
  8. TmpTbl: 
  9. Load Value, 
  10.           Department, 
  11.           load_id as LoadID 
  12.           from IMPALA.Database 
  13.           where not exists(LoadID, load_id); 
  14.  
  15. Concatenate(Table1) 
  16. Load Value, 
  17.           Department, 
  18.           LoadID 
  19.           resident TmpTbl; 
  20. drop table TmpTbl; 


Regards

An Pham

This also only concatenated one row for load_id, but it got me on the right line of thinking. Here's what worked:

  1. [Table1]: 
  2. Load Value, 
  3.           Department, 
  4.           load_id as LoadID 
  5.           from [lib://....qvd](qvd); 
  6.  
  7. noConcatenate 
  8. TmpTbl: 
  9. Load Value, 
  10.           Department, 
  11.           load_id,
  12.           from IMPALA.Database 
  13.           where not exists(LoadID, load_id); 
  14.  
  15. Concatenate(Table1) 
  16. Load Value, 
  17.           Department, 
  18.           load_id as LoadID,
  19.           resident TmpTbl; 
  20. drop table TmpTbl;

It turns out that if you rename load_id to LoadID on the initial load of the TmpTbl, it stops passing the "where not exists" clause after the first row is concatenated. So if you load all of it and change the name when you concatenate it, then it works perfectly.

Thank you for the help!

View solution in original post

2 Replies
kenphamvn
Creator III
Creator III

Hi

Please try this

[Table1]:

Load Value,

          Department,

          load_id as LoadID

          from [lib://....qvd](qvd);

noConcatenate

TmpTbl:

Load Value,

          Department,

          load_id as LoadID

          from IMPALA.Database

          where not exists(LoadID, load_id);

Concatenate(Table1)

Load Value,

          Department,

          LoadID

          resident TmpTbl;

drop table TmpTbl;


Regards

An Pham

elijahabel
Contributor III
Contributor III
Author

An Pham wrote:

Hi

Please try this

  1. [Table1]: 
  2. Load Value, 
  3.           Department, 
  4.           load_id as LoadID 
  5.           from [lib://....qvd](qvd); 
  6.  
  7. noConcatenate 
  8. TmpTbl: 
  9. Load Value, 
  10.           Department, 
  11.           load_id as LoadID 
  12.           from IMPALA.Database 
  13.           where not exists(LoadID, load_id); 
  14.  
  15. Concatenate(Table1) 
  16. Load Value, 
  17.           Department, 
  18.           LoadID 
  19.           resident TmpTbl; 
  20. drop table TmpTbl; 


Regards

An Pham

This also only concatenated one row for load_id, but it got me on the right line of thinking. Here's what worked:

  1. [Table1]: 
  2. Load Value, 
  3.           Department, 
  4.           load_id as LoadID 
  5.           from [lib://....qvd](qvd); 
  6.  
  7. noConcatenate 
  8. TmpTbl: 
  9. Load Value, 
  10.           Department, 
  11.           load_id,
  12.           from IMPALA.Database 
  13.           where not exists(LoadID, load_id); 
  14.  
  15. Concatenate(Table1) 
  16. Load Value, 
  17.           Department, 
  18.           load_id as LoadID,
  19.           resident TmpTbl; 
  20. drop table TmpTbl;

It turns out that if you rename load_id to LoadID on the initial load of the TmpTbl, it stops passing the "where not exists" clause after the first row is concatenated. So if you load all of it and change the name when you concatenate it, then it works perfectly.

Thank you for the help!