topic Re: Use of field values in Where in SQL in Water Cooler

Use of field values in Where in SQL

johncahillhp — Fri, 26 Sep 2014 18:21:02 GMT

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

Re: Use of field values in Where in SQL

maxgro — Fri, 26 Sep 2014 18:40:32 GMT

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;

Re: Use of field values in Where in SQL

anbu1984 — Fri, 26 Sep 2014 18:44:25 GMT

T1:

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

Load Distinct ID from t1.qvd;

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

Re: Use of field values in Where in SQL

johncahillhp — Fri, 26 Sep 2014 18:58:39 GMT

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

Re: Use of field values in Where in SQL

anbu1984 — Fri, 26 Sep 2014 19:02:45 GMT

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);

Re: Use of field values in Where in SQL

johncahillhp — Fri, 26 Sep 2014 20:28:28 GMT

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?

Re: Use of field values in Where in SQL

anbu1984 — Sat, 27 Sep 2014 01:46:47 GMT

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);

Re: Use of field values in Where in SQL

johncahillhp — Sat, 27 Sep 2014 12:23:33 GMT

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

Re: Use of field values in Where in SQL

anbu1984 — Sat, 27 Sep 2014 13:08:51 GMT

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

Re: Use of field values in Where in SQL

anbu1984 — Sat, 27 Sep 2014 16:54:34 GMT

Check this link for Preceding Load

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