topic Re: Help with joining two tables in QlikView

Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 15:39:33 GMT

I have these two tables

Table1:

LOAD * INLINE [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

];

Table2:

LOAD * INLINE [

ID1, ID2, Measure2

024, ABC, 0.7

456, KLM, 0.3

789, GHK, 0.4

];

I am looking to get this as an output

Type	ID1	ID2	Measure1	Measure2
S	123	ABC	20	0.7
E	456	DEF	30	0.3
F	789	GHI	40	0.4

Logic

The basic logic is for Type S join based on ID2 and for all other join based on ID1

Re: Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 15:50:15 GMT

This seems to be working, but I would still be interested in knowing other solutions

Table1:

LOAD *,

If(Type = 'S', Type&'-'&ID2, ID1) as Link;

LOAD * INLINE [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

];

Table2:

LOAD * INLINE [

ID1, ID2, Measure2

024, ABC, 0.7

456, KLM, 0.3

789, GHK, 0.4

];

FinalTable2:

LOAD ID1 as Link,

Measure2

Resident Table2;

Concatenate (FinalTable2)

LOAD 'S-' & ID2 as Link,

Measure2

Resident Table2;

Left Join (Table1)

LOAD Link,

Measure2

Resident FinalTable2;

DROP Table FinalTable2, Table2;

Re: Help with joining two tables

rubenmarin — Wed, 23 Nov 2016 16:13:51 GMT

Hi Sunny, my script (maybe it can't work with real data):

Table1:

LOAD Type, ID1, ID2, Measure1, If(Type='S', ID2, ID1) as Link INLINE [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

];

//Table2:

Left Join (Table1)

LOAD Subfield(ID1 &'#'& ID2, '#') as Link, Measure2 INLINE [

ID1, ID2, Measure2

024, ABC, 0.7

456, KLM, 0.3

789, GHK, 0.4

];

And a question: What if Table 2 has a row with ID1=456 and ID2='ABC'? Can this happen?

Re: Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 16:15:40 GMT

I will try this out. And to answer your question, yes, ID1 = 456 and ID2 = 'ABC' can happen in a single row.

Re: Help with joining two tables

marcus_sommer — Wed, 23 Nov 2016 16:19:15 GMT

I have a different approach which at least work with the few sample-data:

Table2:

crosstable(ID, IDValue, 1)

LOAD Measure2, ID1, ID2 INLINE [

ID1, ID2, Measure2

024, ABC, 0.7

456, KLM, 0.3

789, GHK, 0.4

];

map:

mapping load IDValue, Measure2 resident Table2;

Table1:

LOAD *,

applymap('map', If(Type = 'S', ID2, ID1), '#NV') as Measure2;

LOAD * INLINE [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

];

drop table Table2;

- Marcus

Re: Help with joining two tables

rubenmarin — Wed, 23 Nov 2016 16:23:07 GMT

And what it should do? Join with Type 'E' and Type 'S' of Table1 or some Type has preference?

Re: Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 16:25:23 GMT

I may have incorrectly hinted that ID1 is always numeric and ID2 is always text. They can (very rarely) match also. For instance, like below

Table1:

LOAD * INLINE [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

S, 352, 555, 34

E, 555, KTP, 43

];

Re: Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 16:27:42 GMT

So if Type = S in table1 then join explicitly on ID2 and for all other Types join on ID1. Does that answer your question? I might not have fully understood your question

Re: Help with joining two tables

rubenmarin — Wed, 23 Nov 2016 16:31:26 GMT

If Table 2 has a row with ID1=456 and ID2='ABC' it will join with Type 'S' because ID2='ABC' in both tables and with Type 'E' because ID1=456 in both tables.

To solve having numbers in ID2 and numbers in ID1 (copied from your idea to add an 'S' 😞

Table1:

LOAD Type, ID1, ID2, Measure1, If(Type='S', 'S' & ID2, ID1) as Link Inline [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

];

//Table2:

Left Join (Table1)

LOAD Subfield(ID1 &'#'& 'S' & ID2, '#') as Link, Measure2 Inline [

ID1, ID2, Measure2

024, ABC, 0.7

456, KLM, 0.3

789, GHK, 0.4

];

Re: Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 16:34:38 GMT

This is essentially doing the same thing, but in a much more smaller script. Thanks Ruben!!!

Best,

Sunny

Re: Help with joining two tables

ElizaF — Wed, 23 Nov 2016 16:39:09 GMT

Hi Sunny,

Another solution is to use applymap function.

Table1:

LOAD * INLINE [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

];

Table1_MAP_1:

Mapping

LOAD

If(Type='S',ID2,ID1) AS KEY,

SUM(Measure1) as Measure1

Resident Table1

If(Type='S',ID2,ID1)

;

Table2:

LOAD

ApplyMap('Table1_MAP_1',ID1,0) + ApplyMap('Table1_MAP_1',ID2,0) AS Measure1,

;

LOAD * INLINE [

ID1, ID2, Measure2

024, ABC, 0.7

456, KLM, 0.3

789, GHK, 0.4

050,CC,0.94

];

DROP table Table1;

Re: Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 16:50:37 GMT

Ruben -

Is this still an issue?

If Table 2 has a row with ID1=456 and ID2='ABC' it will join with Type 'S' because ID2='ABC' in both tables and with Type 'E' because ID1=456 in both tables.

I am somehow unable to wrap my head around this. I want to make sure I understand this before I implement yours or mine solution.

Thanks,

Sunny

Re: Help with joining two tables

sunny_talwar — Wed, 23 Nov 2016 16:54:12 GMT

Thank you for your response elizafilip_2014‌. Your script is similar to Marcus, but way more complicated and has some errors. I was able to resolve the errors, but I think I am going to go with Ruben's method.

Thanks for your response once again.

Best,

Sunny

Re: Help with joining two tables

rubenmarin — Wed, 23 Nov 2016 16:54:44 GMT

It can be, I don't know if duplicate the row is the expected result:

Table1:

LOAD Type, ID1, ID2, Measure1, If(Type='S', 'S' & ID2, ID1) as Link Inline [

Type, ID1, ID2, Measure1

S, 123, ABC, 20

E, 456, DEF, 30

F, 789, GHI, 40

];

//Table2:

Left Join (Table1)

LOAD Subfield(ID1 &'#'& 'S' & ID2, '#') as Link, Measure2 Inline [

ID1, ID2, Measure2

024, ABC, 0.7

456, KLM, 0.3

789, GHK, 0.4

456, ABC, 0.1

];

It will retun a duplicated 0.1 Measure2, One for type 'S' (same ID2) and another for Type 'E' (same ID1)