Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Combination of 2 fields to link 2 tables


Hi all,

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

table 1:

Product codeBatch numberreceived date
1A01/01/2013
1B01/02/2013
2A01/06/2013
2C01/06/2013
3B01/03/2014

Table 2:

Product codeBatch NumberDispatch date
1A01/12/2012
1B01/01/2013
2A01/04/2013
2C01/04/2013
2D18/08/2014
3B01/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

1 Solution

Accepted Solutions
Bjorn_Wedbratt
Former Employee
Former Employee

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

View solution in original post

5 Replies
ecolomer
Master II
Master II

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

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

Bjorn_Wedbratt
Former Employee
Former Employee

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

Not applicable
Author

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
Master II
Master II

See Björn replay i that !!

ecolomer
Master II
Master II

See the QV