Problem with join two tables

[Anonymous]

Hello Qliks,

I want to create a key to joining two tables. I want to use outer join(join) to receive all row from Tab1 and Tab2. I think, size of the table shouldn’t be more like sum of all row Tab1 and Tab 2 but I receive like:

Result is 3 747 494

Should be maximum 445 584

I think the problem is with keys, but I don’t know why. I thought it is problem with types, so I did something like below:

Ird:

LOAD

x & '_'& y & '_' & z as %KeySicomToVendor,

x & '_'& y & '_' & z as key1;

LOAD

text(trim(APPLYMAP('MAP_Store_Rest',store_uid))) as x ,

num(date) as y,

text(trim( APPLYMAP('MAP_Vendor',inventory_received_header_uid))) as z

JOIN(Ird)

QSL:

LOAD

x & '_'& y & '_1-' & z as %KeySicomToVendor,

x & '_'& y & '_1-' & z as key2;

LOAD

trim(text( [BK #])) as x,

NUM(DATE(left( [Delivery Date],4)&'-'&mid( [Delivery Date],5,2)&'-'&right( [Delivery Date],2),'YYYY-MM-DD')) as y,

trim(text( APPLYMAP('MAP_Store_Rest_Country',[BK #]))) as z

Could you help me?

Regards

Jacek

[Gysbert_Wassenaar]

I think you may be misunderstanding what JOIN does. Perhaps you want to use CONCATENATE instead. See this discussion for an explanation of the difference: Understanding Join, Keep and Concatenate

[Anonymous]

Hello Gysbert,

I don't want to concatenated tables, I want to join then like the picture below from your link:

Regards

Jacek.

[Gysbert_Wassenaar]

Well, if you say so. It doesn't make sense to me though. You have two tables with exactly the same fields so joining them doesn't add any information. You expect the result table to have the sum of the number of records of the individual tables. All that sounds like concatenating to me. If you join two tables then the common fields will be used to match the records. In your case that means all the fields. If you have duplicate records then each matching record from the other table will be joined with each duplicate:

Table 1:

Field 1	Field 2
1	A
1	A
1	A
1	A

Table 1:

Field 1	Field 2
1	A
1	A
2	B

Result:

Field 1	Field 2
1	A
1	A
1	A
1	A
1	A
1	A
1	A
1	A
2	B