Re: Join not working corectly - Qlik Community

Report Inappropriate Content · ‎2015-08-21

I have trouble joining two different tables. Have searched through community for answers with no results. I do believe that i am missing something.

If i am trying to join some data just inline in script it works perfectly:

TEST:

Load * Inline [

ID,NAME1,SURNAME1,REGION,COUNTRY,NUMBER,T1

1,a,aa,EU,UK,323,T1

2,b,bb,EU,UK,454,T1

1,a,aa,EU,UK,323,T1

2,b,bb,EU,UK,454,T1

];

Join(TEST)

LOAD * Inline [

ID,NAME2,SURNAME2,REGION,COUNTRY,NUMBER,T2

1,a,aa,EU,UK,323,T2

2,b,bb,EU,UK,454,T2

1,a,aa,EU,UK,323,T2

2,b,bb,EU,UK,454,T2

];

And i am getting result which i do want to get:

ID	NAME1	SURNAME1	NAME2	SURNAME2	REGION	COUNTRY	NUMBER	T1	T2
1	a	aa	a	aa	EU	UK	323	T1	T2
2	b	bb	b	bb	EU	UK	454	T1	T2

I do want to create a table with unique combinations from those 2 tables and it works perfectly with Inline.

However, i am loading data from xlsx and the result is not what i have expected

ID	NAME1	SURNAME1	NAME2	SURNAME2	REGION	COUNTRY	NUMBER	T1	T2
1	a	aa			EU	UK	323	T1
1			a	aa	EU	UK	232		T2
2	b	bb			EU	UK	454	T1
2			b	bb	EU	UKJ	454		T2

Here is a simple script that i have:

TABLE:

LOAD

ID, //can repeat

NAME1,

SURNAME1,

REGION,

COUNTRY,

NUMBER,

'T1' as T1 //field for checking if joined correctly

FROM xlsx file

Join(TABLE)

LOAD

ID, //can repeat

NAME2,

SURNAME2,

REGION,

COUNTRY,

NUMBER,

'T2' as T2 //field for checking if joined correctly

FROM other xlsx file

There is no unique IDs in both tables from xlsx.

There are some lines that matches in both xlsx's.

I ma just trying to find some hidden hint which i have missed.

Help is very appreciated.

alexandros17 · ‎2015-08-21

Try with:

TABLE:

LOAD

ID, //can repeat

NAME1,

SURNAME1,

REGION,

COUNTRY,

NUMBER,

'T1' as T1 //field for checking if joined correctly

FROM xlsx file

Join(TABLE)

LOAD

ID, //can repeat

NAME2,

SURNAME2,

REGION as REGION2,

COUNTRY as COUNTRY2,

NUMBER as NUMBER2,

'T2' as T2 //field for checking if joined correctly

FROM other xlsx file

let me know

jonathandienst · ‎2015-08-21

Firstly, the default join in QV is a full outer join.

Secondly, Your join key is NOT ID - it is a composite of ID, REGION, COUNTRY and NUMBER.

Thirdly - no rows in Table 2 match any rows in Table 1 for this composite key

So the rows from Table2 are effectively being concatenated onto Table1.

Perhaps REGION, COUNTRY and NUMBER should not be part of the join key? If you want to keep them, you will need to alias the field names like you did with NAME1/NAME2.
Or do not load them if you do not need these extra fields.
Or look for a data set where there are composite keys in both tables

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

sasiparupudi1 · ‎2015-08-21

Try

QUALIFY REGION,COUNTRY;

TABLE:

LOAD

ID, //can repeat

NAME1,

SURNAME1,

REGION,

COUNTRY,

NUMBER,

'T1' as T1 //field for checking if joined correctly

FROM xlsx file

Join(TABLE)

LOAD

ID, //can repeat

NAME2,

SURNAME2,

REGION,

COUNTRY,

NUMBER,

'T2' as T2 //field for checking if joined correctly

FROM other xlsx file

hth

Sasi

maxgro · ‎2015-08-21

are you sure the result from inline is what you want?

in table viewer the result using your script (with inline) is 8 records

if you load from inline or from excel or from ..... the same data you get the same result

tobias_klett · ‎2015-08-21

Hi Danas,

your join seperates the lines because the kombinations are not the same.

Hope this helps

Regards Tobias

Report Inappropriate Content · ‎2015-08-22

Thanks Tobias for noticing, that was my typing mistake, the field you have showed should be the same.

Thanks Alessandro for suggestion. However, this information is loaded from Sales opportunities and Purchase opportunities. Name and Surname can differ due to typing by users. There is a master file which contains IDs attached to Name and surname. How ever Region, Country and Number is not available for mistake information. I want to have a common information added to the same table from several tables where Name and Surname can differ, other information will be exact the same. As for ID it can be attached to many combinations of Region, Country and number.

During my experiments i have found if i am removing Number from Load information is joined correctly. Number field in database contains Letter, numbers and symbols like "/,-,\" etc.

Thank Jonathan for explanation. I still do not have a full scope of final result that i am trying to reach. But for time being it seems for me that this is a good way regarding the databases and information i deal with. At the moment it seems that the number field is messing with my Join somehow. I really appreciate your suggestions.

Let me get back on other suggestions a bit later.

Report Inappropriate Content · ‎2015-08-22

I have found that if not adding "number" into load, Join works perfectly.

Join with "NUMBER"

ID	NAME1	SURNAME1	NAME2	SURNAME2	REGION	COUNTRY	NUMBER
2446	STE1	REC1			EU	UK	P-13/456/12345
2446			STE2	REC2	EU	UK	P-13/456/12345
2447	STE1	REC1			EU	UK	P-13/456/12346
2447			STE2	REC2	EU	UK	P-13/456/12346
2464	AQU1	SMI1			EU	LT	P-13/456/12347
2464			AQU2	SMI2	EU	LT	P-13/456/12347
2465	AQU1	SMI1			EU	LT	P-13/456/12348
2465			AQU2	SMI2	EU	LT	P-13/456/12348
2469	AQU1	SMI1			EU	LT	P-13/456/12349
2469			AQU2	SMI2	EU	LT	P-13/456/12349

Joint not loading "Number":

ID	NAME1	SURNAME1	NAME2	SURNAME2	REGION	COUNTRY
2446	STE1	REC1	STE2	REC2	EU	UK
2447	STE1	REC1	STE2	REC2	EU	UK
2464	AQU1	SMI1	AQU2	SMI2	EU	LT
2465	AQU1	SMI1	AQU2	SMI2	EU	LT
2469	AQU1	SMI1	AQU2	SMI2	EU	LT

The Question is, maybe there is some issues with data field properties? I am not good in Qlikview, so trying to find any possibilities.

Regarding QUALIFY, i will read a bit more about this function before answering.

I need to understand how to use it.

Anonymous · ‎2015-08-22

Hi Danas,

Please post sample data,so can help.

Regards

Neetha

Report Inappropriate Content · ‎2015-08-24

I believe i have replicated issue on the small scale.

Here is the data

Table1

ID	NAME1	SURNAME1	REGION	COUNTRY	NUMBER
2446	STER	RECK	EU	UK
2447	STER	RECK	EU	UK
2469	AQUA	SMIT	EU	LT
2464	AQUA	SMIT	EU	LT
2465	AQUA	SMIT	EU	LT
2286	OSTE	VITA	EU	UK
2496	NATR	SERV	EU	IE
2576	OSTR	PHAR	EU
2628	COLG		EU	UK
704	LOCO	ASTE	EU	UK	PL0166/0058
704	LOCO	ASTE	EU	UK	PL0166/0058
2023	BEEC	GLAX	EU	UK	PL00079/0280
2026	BETN	GLAX	EU	UK	PL10949/0014
2024	BEEC	GLAX	EU	UK	PL00079/0386
2025	BEEC	GLAX	EU	UK	PL00079/0385

Table2

ID	NAME2	SURNAME2	REGION	COUNTRY	NUMBER
206	KETO	(LEK	EU	MD
206	KETO	(LEK	EU	UA
228	PALI	(LEK	EU	MD
228	PALI	(LEK	EU	UA
319	TROX	(BAL	EU	MD
329	LINE	(LEK	EU	MD	PL0166/0058
329	LINE	(LEK	EU	UA
363	PERS	(SAN	EU	MD
363	PERS	(SAN	EU	UA
873	PERS	(SAN	EU	MD
873	PERS	(SAN	EU	UA
1005	DOXY		EU	RO
1005	DOXY		EU	RO
2446	STER	RECK	EU	UK
2447	STER	RECK	EU	UK
2464	AQUA	SMIT	EU	LT
2465	AQUA	SMIT	EU	LT
2469	AQUA	SMIT	EU	LT

The script that i am using:

OPP:

LOAD

ID & '-' & REGION & '-' & COUNTRY & '-' & NUMBER as PREKE.ID,

ID,

NAME1,

SURNAME1,

REGION,

COUNTRY,

NUMBER

FROM table1.xlsx

Join(OPP)

LOAD

ID & '-' & REGION & '-' & COUNTRY & '-' & NUMBER as PREKE.ID,

ID,

NAME2,

SURNAME2,

REGION,

COUNTRY,

NUMBER

FROM table2.xlsx