# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor

## Prorate sale amount

Hello everybody,

I don't know if it's possible to do what I want but, here it is:

What I have is:

keyclientamount
1A1000
1B1000
1C2000
2A1000
2D500

What I want is:

keyclientamountamoun_prorated
1A1000amount1A + 0,5*amount1B + 0,5*amount1C = 2500
1B10000,5*amount1B = 500
1C20000,5*amount1C =1000
2A1000amount2A + 0,5*amount2D = 1250
2D5000,5*amount2D = 250

So, I explain myself:

• amount_prorated for client A is the Sum of the 50% of each amount where it's key equals.
• amount_prorated for another client is the 50% of his amount if exists client A for its same key.

I don't know if it's clear of not...

Andoni

3 Replies MVP

## Re: Prorate sale amount

Try this

Table:

client,

amount,

If(client = 'A', amount, amount/2) as temp_amount;

key, client, amount

1, A, 1000

1, B, 1000

1, C, 2000

2, A, 1000

2, D, 500

];

Left Join (Table)

'A' as client,

Sum(temp_amount) as NewAmount

Resident Table

Where client <> 'A'

Group By key;

FinalTable:

client,

amount,

RangeSum(temp_amount, NewAmount) as amount_prorated

Resident Table;

DROP Table Table;

New Contributor

## Re: Prorate sale amount

Hi Sunny,

I think I forgot something. If there's no A client mixed with others with the same Key, amount_prorated should be 100% of each client. I give an example modifying the data used above (new data in bold):

keyclientamount
1A1000
1B1000
1C2000
2A1000
2D500
3B500
3F1000

keyclientamountamoun_prorated
1A1000amount1A + 0,5*amount1B + 0,5*amount1C = 2500
1B10000,5*amount1B = 500
1C20000,5*amount1C =1000
2A1000amount2A + 0,5*amount2D = 1250
2D5000,5*amount2D = 250
3B500= amount3B = 500
3F1000= amount3F = 1000

Andoni MVP

## Re: Prorate sale amount

Try this

Table:

client,

amount;

key, client, amount

1, A, 1000

1, B, 1000

1, C, 2000

2, A, 1000

2, D, 500

3, B, 500

3, F, 1000

];

Left Join (Table)

Sum(amount/2) as NewAmount1

Resident Table

Where client <> 'A'

Group By key;

Left Join (Table)

Sum(amount/2) as NewAmount2

Resident Table

Where client = 'A'

Group By key;

FinalTable: