topic Avoiding sync tables in QlikView

Avoiding sync tables

Wed, 20 May 2009 18:53:28 GMT

Hi,

A general question, regarding sync tables. I´m basicaly trying to create a "global variable". So if I have a table with customer information and another one with item information, and i would like to have both of those lists available for two or more tables(Like sales kpi) in pretty much the fashion displayed below (don´t ask why i would want to use these values, they are just for illustration).

Sales

/ \
Customer Items

\ /

Purchasing

Normally if you do this, Qlikview makes a synthetic keys for these. I´ve naming the fields differently ( as customer1, customer2...) which makes a loop in the datamodel. And joining the sales and purchacing tables so that only one link would be needed doesn´t seem to work...

If there´s anyone with any ideas, I would gladly welcome them.

Avoiding sync tables

Wed, 20 May 2009 21:38:37 GMT

You can avoid looping the datamodel by using "qualify", and "unqualify" to make sure field names are different except those you "unqualify".

You would load the needed tables more than once (once for each table where you need the associated to), with different column names. You will specify how they link (to the sales table for example) by controling the field names.

Let me know if this makes sense. I can add an example if needed.

Avoiding sync tables

Anonymous — Wed, 20 May 2009 23:32:12 GMT

Pjotr,
Concatenate Sales and Purchasing instead of join.
Another approach - create a key field in Sales and Purchasing Customer&Item (you can miminize it later if needed), and a link table containing Customer, Item, and Customer&Item. This way you'll get a more complex data model, that is slower calculations. I'd recommend the first one.

Avoiding sync tables

Wed, 20 May 2009 23:42:23 GMT

I would suggest 'Concatenate' statement in your script...

Regards

Eraaj

Avoiding sync tables

Thu, 21 May 2009 04:04:20 GMT

Thanks,
I was actually just thinking that the only thing I haven´t tried is concatenating, so when I get back to my Qlikview I´m bound to try something like that.
Thanks alot for the tip!

Avoiding sync tables

Thu, 21 May 2009 04:07:18 GMT

Eugene,

Thanks for the idea, but the qualify statement didn´t help me out in this case, I tried that earlier on and the selections wouldn´t transfer as i would have liked them to.

Avoiding sync tables

Fri, 19 Jun 2009 12:11:48 GMT

Hi,

I would like to know,if concatenate howabout t he reload time(Data refresh time)????

Regards,

Smita

Avoiding sync tables

Tue, 21 Jul 2009 20:20:35 GMT

In the cases that i have used it it really doesn´t seem to affect too much.I haven´t used any programs to actually measure the exact load times or anything like that, But the effect seems to be quite minimal. Just remember to use the autonumbering, when concatenating a key, so it will be a little bit lighter to use.