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

[jonathandienst]

It is highly likely that the combination of the join fields (same field names in both tables) is not unique in one or both tables. This can cause new rows to be generated in a join.

The common fields are

Country,

Comment_New,

BU,

Entitlement,

Flag,

YearMonth,

[bharatkishore]

Hi Jonathan,

To avoid it can you suggest me the approach what i need to do

[bharatkishore]

Can you please tell me the process how to do it..

[bharatkishore]

stalwar1‌

Can you please help Sunny Bhai..

[sunny_talwar]

I think everyone have already mentioned that there seems to be many to many join here which might be causing the number of rows to increase... which cause your measure to multiply. Look at a very simple example here

Table1:

Dim1,    Value1

A,          10

A,          20

A,          30

Table2:

Dim1,     Value2

A,           20

A,           30

When you join these two tables, you will get this

Dim1,     Value1,     Value2

A,          10,               20

A,          20,               20

A,          30,               20

A,          10,               30

A,          20,               30

A,          30,               30

Now you can clearly see that if you do Sum(Value1), it will give you 120 instead of 60 you expected. and Sum(Value2) will be 150 instead of 50.

To find if this is true or not... run the two files separately and create a straight table with key fields you are joining on as dimensions

Dimension

KeyField1

KeyField2

KeyField3

KeyField4

Expression

Count(DISTINT KeyField1&KeyField2&KeyField3&KeyField4)

If you are seeing rows where the expression is giving you more than 1, then you see your problem right away.

HTH

Best,

Sunny

[bharatkishore]

Thank you sunny bhai..

I am attaching he qvw.. can you please check and tell me where i am doing wrong..

[sunny_talwar]

See how many times your key field is repeating

If the goal is to just fix the sum, you can try this

=Sum(Aggr(Sum(DISTINCT ADJ_CONS_YTD_LY), Plant, Country, Comment_New, BU, Entitlement, YearMonth))

[bharatkishore]

Thank you sunny Bhai... But i need to do this in the script it-self..

Is there any solution you recommend..

[sunny_talwar]

Keep them in two different tables and don't join them.

[bharatkishore]

I need to do by merging into one table because  in the next stage i need at single table.

I am attaching the qvd's as well so that it will be easy for you. Please let me know if you need anything more.

Sorry to take your time..