topic Re: Spread total amount over multiple produkt groups in QlikView

Spread total amount over multiple produkt groups

bengan74 — Thu, 17 Nov 2016 10:03:43 GMT

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

Re: Spread total amount over multiple produkt groups

kaushiknsolanki — Thu, 17 Nov 2016 10:13:42 GMT

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

Re: Spread total amount over multiple produkt groups

bengan74 — Thu, 17 Nov 2016 10:55:13 GMT

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