topic Re: Join in QlikView

Join

bharatkishore — Wed, 16 May 2018 08:49:54 GMT

Hi All,

I have the following code:

T1:

LOAD distinct Plant,

If(Country='Korea, Republic of','Korea',

if(Country='Russian Federation','Russia',

if(Country='United Arab Emirates','Arab Emirates',

if(Country='United States','USA',

if(Country='Viet Nam','Vietnam',Country))))) as Country,

Comment_New,

BU,

District,

[System Code],

Entitlement,

[Material Groups],

Flag,

YearMonth,

Cons_YTD,

Cons_YTD_LY

FROM

[..\5_QVD\IS_QVD4\Modality_Report_temp14bb.qvd]

(qvd);

left join(T1)

LOAD distinct Plant,

Part_Type as Comment_New,

Contract_Type as Entitlement,

BUEqui as BU,

// Country,

Strategic_Flag as Flag,

YearMonth,

// Fiscal_Month,

// Calendar_Year,

ITM_Consumption,

ITM_IB,

YTD_Consumption,

YTD_IB,

YTD_Consumption_LY ,

YTD_IB_LY,

ADJ_CONS_YTD_LY,

// Country_CR,

// Country_Final_temp,

If(Country_Final='Korea, Republic of','Korea',

if(Country_Final='Russian Federation','Russia',

if(Country_Final='United Arab Emirates','Arab Emirates',

if(Country_Final='United States','USA',

if(Country_Final='Viet Nam','Vietnam',Country_Final))))) as Country

FROM

[..\5_QVD\IS_QVD4\Adj_Factors_for_Modality_report_Query4.qvd]

(qvd);

But when i do a left join the values are getting wrong for ADJ_CONS_YTD_LY. Before join i am getting around 497k but after join i am getting 2511K

Can you please help me where i am doing wrong.

Thanks,

Bharat

Re: Join

YoussefBelloum — Wed, 16 May 2018 08:57:59 GMT

Hi,

it is normal using a left join, it is just because that field is present on the second table, so depending on the lines present on the first table, you're maybe not loading all the rows of table2

if you do a right join, I'm sure you'll find 497k.

Maybe should re-think about the join you need on this case ?

Re: Join

bharatkishore — Wed, 16 May 2018 09:07:10 GMT

Hi,

I have tried the right join as well but still i am getting around 2511K.

Is ther any other thing i can do. Please let me know

Re: Join

YoussefBelloum — Wed, 16 May 2018 09:17:49 GMT

Sorry I was reading 251K instead of 2511K...

when you load only table2 you're getting 497k ?

in this case, maybe I'll try different join types (including JOIN)

Re: Join

olivierrobin — Wed, 16 May 2018 09:28:05 GMT

hello

that could mean you rows of table 2 are linked to more than 1 row of table 1

the join is done using all common columns of both tables used in the load statement

Re: Join

big_dreams — Wed, 16 May 2018 09:32:19 GMT

check granularity of your key??

This will happen when key is not correct which you are using for joining.

What is your joining key?

Regards,

Re: Join

keerthika — Wed, 16 May 2018 09:38:14 GMT

Hi,

If you do right join you can get all the columns from table2 whether that column is present in table1 or not. Pls load the table2 alone and check its fetching 2511k

Re: Join

bharatkishore — Wed, 16 May 2018 09:41:43 GMT

Even Join is not working.. only concatenate is working.. But i cannot use it. Can you suggest me a better approach..

Re: Join

bharatkishore — Wed, 16 May 2018 09:42:55 GMT

If i load the second table then i am only getting 497K

Re: Join

bharatkishore — Wed, 16 May 2018 09:43:29 GMT

Can you please suggest me what can i do..

Re: Join

sagar_puri — Wed, 16 May 2018 09:44:06 GMT

Hi Bharat,

Joins will not work in this case because both of them are fact tables and table 2 is linked to more then 1 common field of table 1, you can either concatenate both the tables if they are on the same list level or create a link table between them.

Re: Join

bharatkishore — Wed, 16 May 2018 09:44:52 GMT

Since i am doing a join.. I am having 4 fields in common and few additional fields i am taking from table 2.

Can you please suggest me a better approach..

Re: Join

bharatkishore — Wed, 16 May 2018 09:46:04 GMT

Hi Sagar ,

Concatenate is working but i cannot use bec in the next steps i am calculating few fields..

Re: Join

keerthika — Wed, 16 May 2018 09:46:35 GMT

Can you able to share the table datas

Re: Join

olivierrobin — Wed, 16 May 2018 09:47:22 GMT

are the 4 fields i common the complete key ?

if you want a join 1 row to 1 row, you have to specify all the colums of the unique key

Re: Join

keerthika — Wed, 16 May 2018 09:48:40 GMT

Hi,

Try outer join and check it once...

Re: Join

bharatkishore — Wed, 16 May 2018 09:50:17 GMT

Outer join also 2511K

Re: Join

YoussefBelloum — Wed, 16 May 2018 09:52:50 GMT

bharatkishore

follow this, the problem is not the join type, it is clear that your joining key is missing something..

Re: Join

big_dreams — Wed, 16 May 2018 09:55:43 GMT

which 4 fields?

for better approach create complex key of it and rename those filed and instead of joining use key feature of qlik i.e. association.

Using association you will avoid duplication of rows.

try above method

Regards

Re: Join

keerthika — Wed, 16 May 2018 09:55:50 GMT

Hi,

Because of joining more than one column between table1 and table2 this occurs. Check the finally fetched datas(i.e after join) whether there is any duplicate occurrence of rows...