Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted
Contributor III
Contributor III

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
Highlighted
Contributor III
Contributor III

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