topic Re: Join not working corectly in QlikView

Join not working corectly

Fri, 21 Aug 2015 10:18:47 GMT

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.

Re: Join not working corectly

alexandros17 — Fri, 21 Aug 2015 13:07:33 GMT

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

Re: Join not working corectly

jonathandienst — Fri, 21 Aug 2015 13:17:04 GMT

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

Re: Join not working corectly

sasiparupudi1 — Fri, 21 Aug 2015 13:24:16 GMT

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

Re: Join not working corectly

maxgro — Fri, 21 Aug 2015 14:21:40 GMT

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

Re: Join not working corectly

tobias_klett — Fri, 21 Aug 2015 14:33:07 GMT

Hi Danas,

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

Hope this helps

Regards Tobias

Re: Join not working corectly

Sat, 22 Aug 2015 13:42:29 GMT

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.

Re: Join not working corectly

Sat, 22 Aug 2015 14:10:58 GMT

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.

Re: Join not working corectly

Anonymous — Sat, 22 Aug 2015 14:23:24 GMT

Hi Danas,

Please post sample data,so can help.

Regards

Neetha

Re: Join not working corectly

Mon, 24 Aug 2015 12:44:07 GMT

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

Re: Join not working corectly

Fri, 28 Aug 2015 05:06:45 GMT

With my skills in Qlikview, QUALIFY does not help solve the issue.

I would really appreciate if somebody can help with this.

I have an idea that Join is not working if there is several lines that are matching.

Re: Join not working corectly

Colin-Albert — Fri, 28 Aug 2015 16:04:44 GMT

Consider using ApplyMap rather than joining the data. Mapping will avoid the risk of duplicating the measures in your data.

See this post for more details Don't join - use Applymap instead

Re: Join not working corectly

sasiparupudi1 — Fri, 28 Aug 2015 18:26:49 GMT

what is the expected output for the above problematic data?