Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bengan74
Contributor III
Contributor III

Spread total amount over multiple produkt groups

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

StoreIDCountryMain groupTotal amount

1

SELadies10000
1SEMen5000
2DELadies15000
2DEMen7500


And another table with fields Country, Main group, Item group and Share %:

Table 2

CountryMain groupItem groupShare %
SELadies160
SELadies240
SEMen1075
SEMen1125
DELadies165
DELadies235
DEMen1055
DEMen1145

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 IDCountryMain groupItem groupAmount
1SELadies16000
1SELadies24000
1SEMen103750
1SEMen111250
2DELadies19750
2DELadies25250
2DEMen104125
2DEMen113375
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

2 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
bengan74
Contributor III
Contributor III
Author

Thank you Kaushik  for the really quick reply, and exactly what I wanted! Didn't know exactly what Left join did.....