Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharatkishore
Creator III
Creator III

Join

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

33 Replies
YoussefBelloum
Champion
Champion

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 ?


bharatkishore
Creator III
Creator III
Author

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

YoussefBelloum
Champion
Champion

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)

olivierrobin
Specialist III
Specialist III

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

big_dreams
Creator III
Creator III

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,

keerthika
Creator II
Creator II

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

bharatkishore
Creator III
Creator III
Author

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

bharatkishore
Creator III
Creator III
Author

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

bharatkishore
Creator III
Creator III
Author

Can you please suggest me what can i do..