Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
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

Tags (1)
1 Solution

Accepted Solutions
Support
Support

Re: Combination of 2 fields to link 2 tables

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

5 Replies
ecolomer
Honored Contributor II

Re: Combination of 2 fields to link 2 tables

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

Support
Support

Re: Combination of 2 fields to link 2 tables

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

Re: Combination of 2 fields to link 2 tables

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
Honored Contributor II

Re: Combination of 2 fields to link 2 tables

See Björn replay i that !!

ecolomer
Honored Contributor II

Re: Re: Combination of 2 fields to link 2 tables

See the QV