Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bengan74
New 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

Re: Spread total amount over multiple produkt groups

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

View solution in original post

2 Replies

Re: Spread total amount over multiple produkt groups

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

View solution in original post

bengan74
New Contributor III

Re: Spread total amount over multiple produkt groups

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