Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Avoiding sync tables

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.

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Avoiding sync tables

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.

7 Replies
Not applicable

Avoiding sync tables

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.

mov
Esteemed Contributor III

Avoiding sync tables

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.

Not applicable

Avoiding sync tables

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

Regards

Eraaj

Not applicable

Avoiding sync tables

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!

Not applicable

Avoiding sync tables

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.

Not applicable

Avoiding sync tables

Hi,

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

Regards,

Smita

Not applicable

Avoiding sync tables

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.

Community Browser