Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a scenario where in my main table is like:
Account_No BSG Volume
A123 1 100
A123 2 50
A123 3 80
A867 1 1000
Now I have another table which is like :
Account_No Sales
A123 850
A867 900
I have to get the sale values in the main table. The problem I am facing is that if I use a Left Join with Sales table my Sales number will get inflated or duplicated since there are more than one same Account No in the main table. Appply map will go the same.
The desired output is:
Account_No BSG Volume Sales
A123 1 100 850
A123 2 50
A123 3 80
A867 1 1000 950
May be try this
Table:
LOAD Account_No,
BSG,
Volume
FROM ...
Left Join (Table)
LOAD Account_No,
1 as BSG,
Sales
FROM ....
If all Account_No always have BSG = 1, the above can work for you
Thanks Sunny, the above is just a Scenario I created. In actual data I will have lot many account numbers like in thousands. Also I will have other fields which I have not included in the above scenarios
And the BSGs will also be many not just 1 or 2. I am ok with mapping the Sales with any BSG
I understand that, but is one of the BSG value = 1 for all the Account_Nos you have?
No it is not going to be always 1. It can be any thing
May be try this then
Temp:
LOAD Account_No,
Sales
FROM ....
Left Join (Temp)
LOAD Account_No,
Min(BSG) as BSG
FROM ....;
Table:
LOAD Account_No,
BSG,
Volume
FROM ...
Left Join (Table)
LOAD Account_No,
BSG,
Sales
Resident Temp;
DROP Table Temp;
Thanks Sunny, this would work perfectly if I have only BSG. I have many other fields which I have not listed in the above example which will again cause sales duplication.
I was thinking of doing like Dense rank partition by (SQL function) Account No and then take Rank 1 in the front end.
Would you be able to share few rows of data with all columns?
Do you want Sales to be associated where volume is max ? There must be some logic right ?