topic Re: How to link two fields from two different files in QlikView

How to link two fields from two different files

Fri, 09 Oct 2015 15:23:44 GMT

Hi All,

I have a quite common issue but I did not find any solution.

Let me explain by this example.

File Alfa with 4 columns

order number, customer, product, value

Each customer can be present several times depending on the orders he made (same product can by ordered several times by the same customer but each order is for a single specific product)

File Beta with 3 columns

customer, product, features

Into file Beta each customer is repeated for each different product he buys.

I would like associate these two files by customer and by product so only when there is a matching of customer and product then I can have the features for that specific order.

These two files contain many rows so I need to do the link into the script or at expression level.

However I do not know how to achieve that.

Any advice?

Thank you

Re: How to link two fields from two different files

Gysbert_Wassenaar — Fri, 09 Oct 2015 15:27:04 GMT

Give the field names the same names and the tables will be linked on those field names.

Re: How to link two fields from two different files

Fri, 09 Oct 2015 16:11:27 GMT

tables automatically link together in qlikview when a field has a common name between the two tables. You must be careful though to make sure that there is only 1 field that is common between 2 tables or you will get a synthetic key.

you can also build a link table, which would be an itnermediate table that links your 2 tables together

there is a godd document that discusses data modeling and keys (joining tables

https://community.qlik.com/servlet/JiveServlet/previewBody/7343-102-1-9596/Best%20Practices%20in%20Data%20Modeling.pdf

Re: How to link two fields from two different files

sudeepkm — Fri, 09 Oct 2015 16:13:13 GMT

you can create a composite key based on your customer and product fields. but you need to be careful as the the field data type are string or varchar and it may have mixed case so the combination may vary as per values.

Re: How to link two fields from two different files

pamaxeed — Fri, 09 Oct 2015 16:16:33 GMT

Composite Key like:

autonumber(customer & product) as KEY_1

in both tables and rename those fields in one of the table, to avoid synthtetic keys.....

Cheers,

Patric