LEFT JOIN

master_student — Mon, 16 Jan 2017 10:11:09 GMT

Hello guys,

I am doing left join in Qlikview based on DN_NUM field. here's y script :

The first table returns 56000

I would like to extract the first name/last name that matchs with DN_NUM of table 1

This left join returns 1 000 000 rows and the information is not available for all DN_NUM in table 1

is this any pb with my script? why it didn't return all infos from table 1?

NoConcatenate
Dummy_bscs_TMP:
LOAD * from (qvd);

NoConcatenate
Dummy_bscs:
LOAD * ,
[%Date ID2] as [DATE_STATUS_BARRING.%Date ID],
[%Date ID3] as [DATE_STATUS_BARRING_DATA.%Date ID]
Resident Dummy_bscs_TMP;
drop table Dummy_bscs_TMP;

// connexion

left join(Dummy_bscs)

load 'a' as flag, "KIT", "ADDRESS","BIRTH_DATE" , "CITY", "FIRST_NAME", "ID_NUMBER", "LAST_NAME","DN_NUM" ;
SELECT k.kit_code as KIT, k.ADDRESS,k.BIRTH_DATE , k.CITY, k.FIRST_NAME, k.ID_NUMBER, k.LAST_NAME,k.MSISDN as "DN_NUM"
FROM xx k

//where rownum <= 10;

;

left join(Dummy_bscs)
load 'a' as flag,"KIT", "ADDRESS","BIRTH_DATE" , "CITY", "FIRST_NAME", "ID_NUMBER", "LAST_NAME","DN_NUM" ;

SELECT k.kit_code as KIT,k.ADDRESS,k.BIRTH_DATE , k.CITY, k.FIRST_NAME, k.ID_NUMBER, k.LAST_NAME,k.MSISDN as "DN_NUM"
FROM xxon k;

//where rownum <= 10

left join(Dummy_bscs)
load 'a' as flag ,"KIT", "ADDRESS","BIRTH_DATE" , "CITY", "FIRST_NAME", "ID_NUMBER", "LAST_NAME","DN_NUM" ;

SELECT k.kit_code as KIT,k.ADDRESS,k.BIRTH_DATE , k.CITY, k.FIRST_NAME, k.ID_NUMBER, k.LAST_NAME,k.MSISDN as "DN_NUM"
FROM xxn k;

//where rownum <= 10

left join(Dummy_bscs)
load 'a' as flag ,"KIT", "ADDRESS","BIRTH_DATE" , "CITY", "FIRST_NAME", "ID_NUMBER", "LAST_NAME","DN_NUM" ;

SELECT k.kit_code as KIT,k.ADDRESS,k.BIRTH_DATE , k.CITY, k.FIRST_NAME, k.ID_NUMBER, k.LAST_NAME,k.MSISDN as "DN_NUM"
FROM cxx k;

//where rownum <= 10
left join(Dummy_bscs)

load 'a' as flag , "KIT", "ADDRESS","BIRTH_DATE" , "CITY", "FIRST_NAME", "ID_NUMBER", "LAST_NAME","DN_NUM" ;

SELECT k.kit_code as KIT,k.ADDRESS,k.BIRTH_DATE , k.CITY, k.FIRST_NAME, k.ID_NUMBER, k.LAST_NAME,k.MSISDN as "DN_NUM"
FROM cxx k

//where rownum <= 10
;

exit script;

Re: LEFT JOIN

kuczynska — Mon, 16 Jan 2017 10:32:34 GMT

Hi,

Can you confirm the names of all columns in Dummy_bscs table? If you don't have a column called exactly the same as the fields from the tables you want to join, QV will perform this join over a key build from as all columns, inserting additional rows and causing the row count to increase. If your key used for left join is unique and build correctly, then you will always match the number of records from before this operation.

Hope this helps. Good luck!

Re: LEFT JOIN

master_student — Mon, 16 Jan 2017 11:00:36 GMT

Thanks Michalina.

There's only one same column between the two tables DN NUM, is that sufficent to do a left join?

Re: LEFT JOIN

kuczynska — Mon, 16 Jan 2017 11:05:41 GMT

Hi,

In general is the ideal scenario but have a look at the format of data from both of your sources - if data comes as text from one place and as num() from the other, this would not work.

Is it the very first join that causes this problem?

topic Re: LEFT JOIN in QlikView

LEFT JOIN

Re: LEFT JOIN

Re: LEFT JOIN

Re: LEFT JOIN