topic connect queries in QlikView

connect queries

qlikhans — Fri, 05 Mar 2010 21:22:24 GMT

Hi,

I want to build a QV but the query becomes too complex. Is it possible to give a queries names, and then connect the queries to eachother, for example as you would do in MSaccess?

thanks

Hans

connect queries

vgutkovsky — Fri, 05 Mar 2010 21:35:08 GMT

Hans,

You can store connection strings as variables like anything else. Then you would use variable expansion to call the string. I believe you can do this at any point, including inside other connection strings.

Regards,

connect queries

qlikhans — Fri, 05 Mar 2010 21:47:45 GMT

Hi Vlad, since I am a newbie, I do not exactly understand what you are advising me here. Would it be possible to post an example?

kndrgrds,

Hans

connect queries

vgutkovsky — Fri, 05 Mar 2010 22:10:41 GMT

Sure. But, I think I misunderstood--you want queries, not connection strings. Can you give me an example of a query you would like to store?

Regards,

connect queries

qlikhans — Fri, 05 Mar 2010 22:37:04 GMT

Is going to be after the weekend. Lost the remote connection to my desktop and it is night time here already, so no one to reset the machine .

Hope you will be on line somewhere next week to help out

kind rgrds

Hans

connect queries

qlikhans — Thu, 11 Mar 2010 16:52:51 GMT

Hi Vlad,

Query1:

Select

CRM5.contact.contact_id,

CRM5.contact.name

As bedrijfsnaam,

CRM5.contact.number2

As klantnummer,

CRM5.udlist.name

As markt,

CRM5.associate.name

As relatiebeheerder,

CRM5.country.name

As land,

CRM5.business.name

As rol,

CRM5.category.name

As categorie

From

CRM5.contact Left Join CRM5.udcontactsmall On CRM5.contact.userdef_id =CRM5.udcontactsmall.udcontactSmall_idLeft JoinCRM5.udlist On CRM5.udcontactsmall.long01 = CRM5.udlist.UDList_id Left Join CRM5.associate On CRM5.contact.associate_id = CRM5.associate.associate_id Left Join CRM5.country On CRM5.contact.country_id = CRM5.country.country_id Left Join CRM5.business On CRM5.contact.business_idx = CRM5.business.business_id Left Join CRM5.category On CRM5.contact.category_idx = CRM5.category.Category_id

this one must be joined to a table which has 'klantnummer' in common. Query 1 has more records than query 2. All records of Query 1 must be included.

connect queries

vgutkovsky — Thu, 11 Mar 2010 20:15:41 GMT

Hans,

So just to clarify, you want to store the "From" part as a variable? If you want to store the entire query, I'm not sure what the point of that would be...

Regards,

connect queries

qlikhans — Fri, 12 Mar 2010 06:06:56 GMT

Hi Vlad,

correct

kind rgrds

Hans

connect queries

vgutkovsky — Fri, 12 Mar 2010 07:34:06 GMT

Hans,

All you need to do is to set this as a variable:

Let vConnection = 'CRM5.contact Left Join CRM5.udcontactsmall On CRM5.contact.userdef_id =CRM5.udcontactsmall.udcontactSmall_idLeft JoinCRM5.udlist On CRM5.udcontactsmall.long01 = CRM5.udlist.UDList_id Left Join CRM5.associate On CRM5.contact.associate_id = CRM5.associate.associate_id Left Join CRM5.country On CRM5.contact.country_id = CRM5.country.country_id Left Join CRM5.business On CRM5.contact.business_idx = CRM5.business.business_id Left Join CRM5.category On CRM5.contact.category_idx = CRM5.category.Category_id';

Then you should be able to call this variable at any point in the script, including in a query:


Select
CRM5.contact.contact_id,
CRM5.contact.name As bedrijfsnaam,
CRM5.contact.number2 As klantnummer,
CRM5.udlist.name As markt,
CRM5.associate.name As relatiebeheerder,
CRM5.country.name As land,
CRM5.business.name As rol,
CRM5.category.name As categorie
From
$(vConnection);

Also keep in mind that if you want to use "AS", you will need a preceding load, which you are currently missing.

Regards,

connect queries

qlikhans — Fri, 12 Mar 2010 07:51:23 GMT

Hi Vlad,

Great! with this answer, I think you have helped me out with more issues I have to deal with.

Kind rgrds

Hans