Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
virilo_tejedor
Creator
Creator

Execute SELECT WHERE id IN(...) programmatically in Load Script

I'd like to interate over a list of M ids to exectue a select M/N times using a batch size of N:

Pseudocode:


[IDS_LIST]:
   LOAD [id]
FROM [lib://POC/today_million_ids_list.csv]


LIB CONNECT TO 'MY_ORACLE_DB';

[TABLE]
for i in M step N:
   SELECT * FROM my_table WHERE table_id in (id[i+1], id[i+2]... id[i+N])

Is it possible to do from a load script?

Thanks in advance

 

1 Solution

Accepted Solutions
virilo_tejedor
Creator
Creator
Author

I solved it with a little bit of this and a little bit of that.

There must be a smarter solution, but it worked for me:


 

trace 'Batches';

LET vNumRows = NoOfRows('TABLE_IN_CSV'); // There musn't be duplicates in this table in order FieldValue not to return some extra 'nulls'
LET vBatchSize=30;
  

for i=1 to $(vNumRows) step vBatchSize // loop through every row
    trace 'Loading $(i) of $(vNumRows) rows.';
    let vWhereInClause = FieldValue('TABLE_ID',$(i)); //get the value for "text" field on each row
    
    vMinJ=$(i)+1
    
    //vMaxJ=Min($(vNumRows), $(i)+$(vBatchSize)-1)
    vMaxJ=if($(vNumRows)< $(i)+$(vBatchSize)-1, $(vNumRows),$(i)+$(vBatchSize)-1)
    
    for j=$(vMinJ) to $(vMaxJ)
     vWhereInClause='$(vWhereInClause)' & ',' & FieldValue('TABLE_ID',$(j))
    next
    
    //trace 'vWhereInClause $(vWhereInClause)';

[MY_ORACLE_TABLE]:
LOAD 
  ...
;SELECT
  *
FROM MY_ORACLE_TABLE
    WHERE TABLE_ID in ($(vWhereInClause))
;


next  

View solution in original post

1 Reply
virilo_tejedor
Creator
Creator
Author

I solved it with a little bit of this and a little bit of that.

There must be a smarter solution, but it worked for me:


 

trace 'Batches';

LET vNumRows = NoOfRows('TABLE_IN_CSV'); // There musn't be duplicates in this table in order FieldValue not to return some extra 'nulls'
LET vBatchSize=30;
  

for i=1 to $(vNumRows) step vBatchSize // loop through every row
    trace 'Loading $(i) of $(vNumRows) rows.';
    let vWhereInClause = FieldValue('TABLE_ID',$(i)); //get the value for "text" field on each row
    
    vMinJ=$(i)+1
    
    //vMaxJ=Min($(vNumRows), $(i)+$(vBatchSize)-1)
    vMaxJ=if($(vNumRows)< $(i)+$(vBatchSize)-1, $(vNumRows),$(i)+$(vBatchSize)-1)
    
    for j=$(vMinJ) to $(vMaxJ)
     vWhereInClause='$(vWhereInClause)' & ',' & FieldValue('TABLE_ID',$(j))
    next
    
    //trace 'vWhereInClause $(vWhereInClause)';

[MY_ORACLE_TABLE]:
LOAD 
  ...
;SELECT
  *
FROM MY_ORACLE_TABLE
    WHERE TABLE_ID in ($(vWhereInClause))
;


next