Combination of 2 fields to link 2 tables

[#1]

Hi all,

I am trying to use a combination of 2 fields to link 2 tables.

table 1:

Product code	Batch number	received date
1	A	01/01/2013
1	B	01/02/2013
2	A	01/06/2013
2	C	01/06/2013
3	B	01/03/2014

Table 2:

Product code	Batch Number	Dispatch date
1	A	01/12/2012
1	B	01/01/2013
2	A	01/04/2013
2	C	01/04/2013
2	D	18/08/2014
3	B	01/01/2013

In excel I would use a concatenate function between Product code and batch number (1A, 1B...) and a lookup to have visibility on disaptch date and received date. I believe the logic in qlikview is a little different

Can someone please help me with this?

Thank you so much in advance

[ecolomer]

You can repeat same as with excel, but if you not change any think, you have this:

and you have a relationship between the two tabalas through two fields

[Bjorn_Wedbratt · Accepted solution]

Hi Pierre,

In Qlik you would concatenate the two fields in your load-statement, using "&" to create a new key-field. This field will then include the values 1A, 1B etc, in similar ways as in Excel.

Example:

Tab1:

Load

[Product code]&[Batch number] as key...

Tab2:

Load

[Product code]&[Batch Number] as key...

Qlik will do an automatic association between the tables using the new key-field, you don't need to do anything yourself (like joining them or similar).

Qlik also have an automatic way of creating concatenated key fields, which will be called synthetic keys. This will be done based on the field names, so if you for example rename "Batch number" in the first table to "Batch Number" (notice Qlik is case-sensitive), you will get an automatic (synthetic) key based on the two fields matching in the two tables.

Note, in some cases you do not want this behaviour as it may lead to undesirable results, so I recommend the first approach. You also need to make sure that you don't get any undesirable associations between the two tables, by renaming fields that should not be used as keys between tables.

Hope this will clarify how Qlik works. It's quite simple, once you get the hang of it

[#4]

Thanks Enrique however I would like to avoid having a Synthetic table.

I would like to have  the dispatch date of Product 1, Batch A  related to the  dispatch date of product 1, Batch A. So I somehow need to make a combinaition of product code and batch number.

[ecolomer]

See Björn replay i that !!

[ecolomer]

See the QV