Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
WeePecky
Contributor III
Contributor III

Load Script - How to use a multi value variable in SQL Statement

Greetings, 

I am trying to use the values in a table as the parameter for an in clause. Something like this: 

Table1:
Load ColumnA Resident Table2;

LET vColumnA = [some function that returns column a];

load * ; 
sql select value from SQLTable where value in '$(vColumnA )';

Is this possible?

Thanks in anticipation. 
Wee.

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

By the suggested exists() approach the whole dataset is pulled from the db and then filtered on the Qlik side. If the datasets are rather small and quick to pull it's no bad solution. But by large datasets it's often not practicable to pull all data which means the filtering must be applied on the sql side.

In your case you could try something like this:

Table1: Load concat(ColumnA, ',') as  ColumnA Resident Table2 where AnyFilter = true();

LET vColumnA = peek('ColumnA', 0, 'Table1');

load * ; 
sql select value from SQLTable where value in('$(vColumnA )');

Depending on the data (numeric, strings, formatted, ...) and the syntax-requirements from your db you may need to add any quotes within your aggregated values to get a proper listing (just check the variable before within a textbox that the listing looked ok). 

Before starting with the above check how many parameters does your db accept within an in() and if it are always more as your value-listing would return. Background is that many db have here restrictions to n parameters (AFAIK most 1000).

If there are more values as you could implement within an in() you couldn't go this way. In this case you need to filter on dates or sequentially ID's or similar where you could apply logics like: date >= value. Depending on you data you may not be able to build an incremental logic without any overlapping of the data what means you will need some logics than on the Qlik side to ensure that no duplicates are loaded within the final tables (sounds more complicated as it is because here you could apply such mentioned exists() approach). 

Another alternatively would be to transfer the data from table 1 into your db - maybe a simple csv-store of it and the db loads it and used it as an inner join filter.

- Marcus

View solution in original post

6 Replies
joaopaulo_delco
Partner - Creator III
Partner - Creator III

Hi @WeePecky !

     I think is better to use a exists in load statement. Like below:

Table1:

Load ColumnA Resident Table2;

 

Load Value1 where Exists(ColumnA,Value1);

SQL Select Value1 from SQLTable; 

Help users find answers! Don't forget to mark a solution that worked for you!
WeePecky
Contributor III
Contributor III
Author

Thanks @joaopaulo_delco 

I did consider this. If I use the Exist statement will the SQL statement return only those rows that are filtered by the Exists clause? I have multiple millions of rows which will be returned if this is not so.

Thanks

PradeepK
Creator II
Creator II

I Understand you want to push filter condition to DB.. You can use below approach to get all the distinct values for field into variable.

Sample Data:

Measures:
Load * inline [
Measure
Gross Sales
Net Sales
TSales
FSales
FSales
PartSales
];

Steps

1. Create temp table. Use Concat() with Group By

Temp:
Load
Concat(distinct Measure, ',') as values
Resident Measures
Group by 1;

2. Use peek() to get values into variable

Let vValues = Peek('values',0,'Temp');

 

Result : 

FieldValue.PNG

 

I hope this answers your question.

PradeepK
Creator II
Creator II

I Understand you want to push filter condition to DB.. You can use below approach to get all the distinct values for field into variable.

Sample Data:

Measures:
Load * inline [
Measure
Gross Sales
Net Sales
TSales
FSales
FSales
PartSales
];

Steps: 

1. Create temp table. Use Concat() with Group By

Temp:
Load
Concat(distinct Measure, ',') as values
Resident Measures
Group by 1;

2. Use peek() to get values into variable

Let vValues = Peek('values',0,'Temp');

 

Result : 

FieldValue.PNG

 Use this variable in SQL query as required.


 

I hope this answers your question.

marcus_sommer

By the suggested exists() approach the whole dataset is pulled from the db and then filtered on the Qlik side. If the datasets are rather small and quick to pull it's no bad solution. But by large datasets it's often not practicable to pull all data which means the filtering must be applied on the sql side.

In your case you could try something like this:

Table1: Load concat(ColumnA, ',') as  ColumnA Resident Table2 where AnyFilter = true();

LET vColumnA = peek('ColumnA', 0, 'Table1');

load * ; 
sql select value from SQLTable where value in('$(vColumnA )');

Depending on the data (numeric, strings, formatted, ...) and the syntax-requirements from your db you may need to add any quotes within your aggregated values to get a proper listing (just check the variable before within a textbox that the listing looked ok). 

Before starting with the above check how many parameters does your db accept within an in() and if it are always more as your value-listing would return. Background is that many db have here restrictions to n parameters (AFAIK most 1000).

If there are more values as you could implement within an in() you couldn't go this way. In this case you need to filter on dates or sequentially ID's or similar where you could apply logics like: date >= value. Depending on you data you may not be able to build an incremental logic without any overlapping of the data what means you will need some logics than on the Qlik side to ensure that no duplicates are loaded within the final tables (sounds more complicated as it is because here you could apply such mentioned exists() approach). 

Another alternatively would be to transfer the data from table 1 into your db - maybe a simple csv-store of it and the db loads it and used it as an inner join filter.

- Marcus

WeePecky
Contributor III
Contributor III
Author

Thanks @marcus_sommer

Thank you for the clarification concerning the use of exists in this context. I have a very large dataset. 
I was not aware that using a "0" value in peek('ColumnA', 0, 'Table1) would return all the values in ColumnA as a list.

Thank you for your response. 

Cheers