Redistribution of values during script load

Fri, 17 Sep 2010 14:32:20 GMT

Hi,

I have the following situation:

1 table, Structure, with Year/Month, BrickCode, Product, Value

1 table, Data, with BrickCode, BrickName, New_Brick, SalesRep_Surname

As you can see in the attached files the New_Brick is defined only for some BrickName and not all bricks related to a SalesRep are groupped in the New_Brick. i.e SalesRep = Rogers as 13 Bricks but only 4 in the New_Brick.

What i need is to distribute the full amount of New_Brick equally to all SalesRep in the New_Brick group.

Example:

For istance full amount of MILANO (New_Brick) is 1.200 where Rogers makes 400 €, Lucas makes 700 € and Coppola makes 100 €.

I need to have the 1.200 € divided equally to the 3 reps: Rogers 400€, Lucas 400 € and Coppola 400 € but in fact they have different number of bricks: Rogers as 4 bricks, Lucas and Coppola 8.

After some test I have defined that I need to recalculate the amount made for each single line in the Data table where:

If New_Brick is null I use Value, if is not null (Milano, Torino, Napoli) I need to divede the full amount for New_Brick (1200) by the number of reps in this macro area (3) and divide the result by the number of records existing in Data table.

i.e if Rogers as 4 bricks in the New_Brick Milano I need to count how many records I have in the table Dati for these bricks (100). Now the formula is 1200 / 3 = 400 / 100 = 4.

Lucas as 200 lines so the formula is 1200 / 3 = 400 / 200 = 2 and Coppola as 150 lines so 1200 / 3 = 400 / 50 = 8.

Finally I have that each line of Rogers as a "new" value of 4, Lucas of 2 and Coppola 8. The full amount is the same but I have redistributed the values equally to the reps in the Milano area.

How to do it in QlikView?

Thanks

topic Redistribution of values during script load in QlikView

Redistribution of values during script load