topic Re: Associate table based on more than 1 column in QlikView

Associate table based on more than 1 column

vireshkolagimat — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I have 2 table which contain 2 matching fields and i want create the association on both the fields.

How to achieve this? In general if i want to create association between 2 or more tables based on more than one column what would be the solution.

table1:

Name

Salary

Date

table2:

Name

City

Designation

Regards,

Viresh

Re: Associate table based on more than 1 column

MK_QSL — Tue, 10 Jan 2017 08:39:02 GMT

table1:

Name,

ID & Name as Key,

Salary

Date

table2:

ID & Name as Key

City

Designation

Re: Associate table based on more than 1 column

pathiqvd — Tue, 10 Jan 2017 09:06:47 GMT

Hi,

Try This,

table1:

ID as ID1

Name as Name1

Salary

autonumber(ID&'-'&Name) as Key

'table1' as Type

Date

table2:

ID as ID2

Name as Name2

City

autonumber(ID&'-'&Name) as Key

Designation

'table2' as Type

take common table:-

Common_temp:

load

ID1 as ID,

Name1 as Name

Type as Common_Type

resident table1;

concatenate

Load

ID2 as ID

Name2 as Name

Type as Common_Type

resident table2;

Common:

LOAD *,

autonumber(ID&'-'&Name) as Key

Resident Common_temp;

drop table Common_temp;

drop field Type;

Regards,

Re: Associate table based on more than 1 column

Tue, 10 Jan 2017 09:12:57 GMT

Hi viresh,

In qlikview, if any two (Or more) tables contain same common field names qlikview automatically create the association .

Re: Associate table based on more than 1 column

vireshkolagimat — Tue, 10 Jan 2017 09:33:24 GMT

Yeah but it will generate synthetic key. I want association to happen on two columns without creating synthetic key.

Re: Associate table based on more than 1 column

Anonymous — Tue, 10 Jan 2017 09:40:00 GMT

Hi Viresh,

You can try this

table1:

Name,

Autonumberhash256 (ID , Name as) Key,

Salary

Date

table2:

Autonumberhash256 (ID , Name as) Key,

City

Designation

So,it will use less memory.

-Jai

Re: Associate table based on more than 1 column

dsharmaqv — Tue, 10 Jan 2017 10:03:58 GMT

If you want to go for association then it will create synthetic key.

incase you want to avoide that then you should look for joins.