Use of field values in Where in SQL

[johncahillhp]

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

[maxgro]

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]

T1:

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

Load Distinct ID from t1.qvd;

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

[johncahillhp]

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]

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]

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 · Accepted solution]

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]

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]

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

[anbu1984]

Check this link for Preceding Load

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load