topic Re: Problem with join two tables in QlikView

Problem with join two tables

Anonymous — Wed, 06 Dec 2017 08:16:50 GMT

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

Re: Problem with join to tables

Gysbert_Wassenaar — Wed, 06 Dec 2017 08:21:21 GMT

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

Re: Problem with join two tables

Anonymous — Wed, 06 Dec 2017 08:24:57 GMT

Hello Gysbert,

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

Regards

Jacek.

Re: Problem with join two tables

Gysbert_Wassenaar — Wed, 06 Dec 2017 08:45:06 GMT

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