Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Join not working corectly

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:

IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRYNUMBERT1 T2
1aaaaaaEUUK323T1T2
2bbbbbbEUUK454T1T2

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

IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRYNUMBERT1T2
1aaaEUUK323T1
1aaaEUUK232T2
2bbbEUUK454T1
2bbbEUUKJ454T2

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.

12 Replies

Re: Join not working corectly

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

MVP
MVP

Re: Join not working corectly

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
Honored Contributor III

Re: Join not working corectly

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

MVP
MVP

Re: Join not working corectly

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

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

1.png

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

tobias_klett
Contributor II

Re: Join not working corectly

Hi Danas,

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

Join_Error.png

Hope this helps

Regards Tobias

Not applicable

Re: Join not working corectly

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.

Not applicable

Re: Join not working corectly

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

Join with "NUMBER"

        

IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRYNUMBER
2446STE1REC1EUUKP-13/456/12345
2446STE2REC2EUUKP-13/456/12345
2447STE1REC1EUUKP-13/456/12346
2447STE2REC2EUUKP-13/456/12346
2464AQU1SMI1EULTP-13/456/12347
2464AQU2SMI2EULTP-13/456/12347
2465AQU1SMI1EULTP-13/456/12348
2465AQU2SMI2EULTP-13/456/12348
2469AQU1SMI1EULTP-13/456/12349
2469AQU2SMI2EULTP-13/456/12349

Joint not loading "Number":      

IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRY
2446STE1REC1STE2REC2EUUK
2447STE1REC1STE2REC2EUUK
2464AQU1SMI1AQU2SMI2EULT
2465AQU1SMI1AQU2SMI2EULT
2469AQU1SMI1AQU2SMI2EULT

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.

neetha_p
Honored Contributor

Re: Join not working corectly

Hi Danas,

Please post sample data,so can help.

Regards

Neetha

Not applicable

Re: Join not working corectly

I believe i have replicated issue on the small scale.

Here is the data

Table1      

IDNAME1SURNAME1REGIONCOUNTRYNUMBER
2446STERRECKEUUK
2447STERRECKEUUK
2469AQUASMITEULT
2464AQUASMITEULT
2465AQUASMITEULT
2286OSTEVITAEUUK
2496NATRSERVEUIE
2576OSTRPHAREU
2628COLGEUUK
704LOCOASTEEUUKPL0166/0058
704LOCOASTEEUUKPL0166/0058
2023BEECGLAXEUUKPL00079/0280
2026BETNGLAXEUUKPL10949/0014
2024BEECGLAXEUUKPL00079/0386
2025BEECGLAXEUUKPL00079/0385

Table2      

IDNAME2SURNAME2REGIONCOUNTRYNUMBER
206KETO(LEKEUMD
206KETO(LEKEUUA
228PALI(LEKEUMD
228PALI(LEKEUUA
319TROX(BALEUMD
329LINE(LEKEUMDPL0166/0058
329LINE(LEKEUUA
363PERS(SANEUMD
363PERS(SANEUUA
873PERS(SANEUMD
873PERS(SANEUUA
1005DOXYEURO
1005DOXYEURO
2446STERRECKEUUK
2447STERRECKEUUK
2464AQUASMITEULT
2465AQUASMITEULT
2469AQUASMITEULT

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

Community Browser