Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
bharatkishore
Contributor 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
Esteemed Contributor

Re: Join

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

Re: Join

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
Esteemed Contributor

Re: Join

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
Valued Contributor II

Re: Join

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

Re: Join

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
Contributor II

Re: Join

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

Re: Join

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

bharatkishore
Contributor III

Re: Join

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

bharatkishore
Contributor III

Re: Join

Can you please suggest me what can i do..