Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
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
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 :
I hope this answers your question.
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 :
Use this variable in SQL query as required.
I hope this answers your question.
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
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