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: 
johncahillhp
Contributor III
Contributor III

Use of field values in Where in SQL


Hello,

I am pulling data from two data sources into one QVD file. Is there a way to use values from data source one in the Where In clause of the SQL for data source 2?

Clearly very wrong, but this is how I envision it working. However enlighten me if there is a better way.

T1:

Load Distinct ID from t1.qvd;

Let vIDs =  ID;

Drop Table T1;

T2:

SQL

Select ID, Name from mytable Where ID in ($(vIDs);

Store T2 into t2.qvd (qvd);

Drop Table T2;

Thank you,

John

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

Try this


Load Concat(Distinct Chr(39) & AST_VRF_PROD_OID & Chr(39),',') As AST_VRF_PROD_OID;
Load AST_VRF_PROD_OID from ICMLog.qvd (QVD);
Let strOIDs = FieldValue('AST_VRF_PROD_OID',1);



View solution in original post

9 Replies
maxgro
MVP
MVP

maybe yes if you concatenate all ID from T1 and the variable is 'v1,v2,v3,......'

so you can use the variable in SQL select

this is a different solution

T1:

Load Distinct ID from t1.qvd;

// get all rows from sql, no filter (SQL Select....)

// filter the rows returned by sql (Load * ....)

T2: 

Load * where exists(ID);

SQL Select ID, Name from mytable;

Store T2 into t2.qvd (qvd);

Drop Table T1;

anbu1984
Master III
Master III

T1:

Load Concat(Chr(39) & IDs & Chr(39),',') As ID;

Load Distinct ID from t1.qvd;

Let vIDs = FieldValue('ID',1);

johncahillhp
Contributor III
Contributor III
Author

Can you confirm the difference between IDs in the first LOAD and ID in the remaining code? Is the IDs a variable or the field ID?

Thanks

anbu1984
Master III
Master III

It was typo. It should be ID field from QVD.

T1:

Load Concat(Chr(39) & ID & Chr(39),',') As ID;

Load Distinct ID from t1.qvd;

Let vIDs = FieldValue('ID',1);

johncahillhp
Contributor III
Contributor III
Author

Hi Abnu,

This is the exact code I've implemented;

Load Concat(Chr(39) & AST_VRF_PROD_OID & Chr(39),',') As AST_VRF_PROD_OID;
Load Distinct AST_VRF_PROD_OID from ICMLog.qvd (QVD);
Let strOIDs = FieldValue('AST_VRF_PROD_OID',1);

The resultant variable strOID contains repetitive values. Here is a sample as the string is very long and fails within the SQL statement.

'','','','','','','','','','','','241642','241642','241642','241642','241642','241642','241642'

Thoughts?

anbu1984
Master III
Master III

Try this


Load Concat(Distinct Chr(39) & AST_VRF_PROD_OID & Chr(39),',') As AST_VRF_PROD_OID;
Load AST_VRF_PROD_OID from ICMLog.qvd (QVD);
Let strOIDs = FieldValue('AST_VRF_PROD_OID',1);



johncahillhp
Contributor III
Contributor III
Author

Excellent! Thanks for this.

strOIDs "'241642','241644','241646','3200137','321933'"

Now tell me how this works. First I've seen LOAD used in this fashion.

John

anbu1984
Master III
Master III

This type of Load is called Preceding Load. Concat function in preceding load concatenates all ID from QVD.

Check this link for the same

www.quickintelligence dot co dot uk/preceding-load-qlikview