Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
Hi Jonathan,
To avoid it can you suggest me the approach what i need to do
Can you please tell me the process how to do it..
Can you please help Sunny Bhai..
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
Thank you sunny bhai..
I am attaching he qvw.. can you please check and tell me where i am doing wrong..
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))
Thank you sunny Bhai... But i need to do this in the script it-self..
Is there any solution you recommend..
Keep them in two different tables and don't join them.
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..