Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need some help before I go nuts 🙂
I'm having one table with fields StoreID, Country, Main group and Total amount:
Table 1
StoreID | Country | Main group | Total amount |
---|---|---|---|
1 | SE | Ladies | 10000 |
1 | SE | Men | 5000 |
2 | DE | Ladies | 15000 |
2 | DE | Men | 7500 |
And another table with fields Country, Main group, Item group and Share %:
Table 2
Country | Main group | Item group | Share % |
---|---|---|---|
SE | Ladies | 1 | 60 |
SE | Ladies | 2 | 40 |
SE | Men | 10 | 75 |
SE | Men | 11 | 25 |
DE | Ladies | 1 | 65 |
DE | Ladies | 2 | 35 |
DE | Men | 10 | 55 |
DE | Men | 11 | 45 |
Now's the tricky part; I'd like to take each row in Table 1 and spread the total amount to occurring item group in Table 2 where field Country and Main group matches (Total Amount x Share %). So f.e. the first row in Table 1 should be split into 2 rows depending on there's 2 different item groups in country "SE" and Main group "Ladies".
So new Table 3 should have fields Store ID, Country, Main group, Item group and new field Amount:
Store ID | Country | Main group | Item group | Amount |
---|---|---|---|---|
1 | SE | Ladies | 1 | 6000 |
1 | SE | Ladies | 2 | 4000 |
1 | SE | Men | 10 | 3750 |
1 | SE | Men | 11 | 1250 |
2 | DE | Ladies | 1 | 9750 |
2 | DE | Ladies | 2 | 5250 |
2 | DE | Men | 10 | 4125 |
2 | DE | Men | 11 | 3375 |
Hi,
Try this,
DATA:
Load StoreID,Country,MainGroup,TotalAmount,Country&'-'&MainGroup as KEY From xyz;
Left join (DATA)
Load Country&'-'&MainGroup as KEY,ItemGroup,Share from PQR;
FinalData:
Load StoreID,MainGroup,Country,ItemGroup,TotalAmount * (Share/100) as Amount Resident DATA;
Drop table DATA;
Regards,
Kaushik Solanki
Hi,
Try this,
DATA:
Load StoreID,Country,MainGroup,TotalAmount,Country&'-'&MainGroup as KEY From xyz;
Left join (DATA)
Load Country&'-'&MainGroup as KEY,ItemGroup,Share from PQR;
FinalData:
Load StoreID,MainGroup,Country,ItemGroup,TotalAmount * (Share/100) as Amount Resident DATA;
Drop table DATA;
Regards,
Kaushik Solanki
Thank you Kaushik for the really quick reply, and exactly what I wanted! Didn't know exactly what Left join did.....