Join

[bharatkishore]

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

[sagar_puri]

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.

[bharatkishore]

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..

[bharatkishore]

Hi Sagar ,

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

[keerthika]

Can you able to share the table datas

[olivierrobin]

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

[keerthika]

Hi,

     Try outer join and check it once...

[bharatkishore]

Outer join also 2511K

[YoussefBelloum]

bharatkishore

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

[big_dreams]

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

[keerthika]

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...