Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andonialbisu
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...

Thanks in advance.

Andoni

3 Replies

Re: Prorate sale amount

Try this

Table:

LOAD key,

client,

amount,

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

LOAD * INLINE [

    key, client, amount

    1, A, 1000

    1, B, 1000

    1, C, 2000

    2, A, 1000

    2, D, 500

];


Left Join (Table)

LOAD key,

'A' as client,

Sum(temp_amount) as NewAmount

Resident Table

Where client <> 'A'

Group By key;


FinalTable:

LOAD key,

client,

amount,

RangeSum(temp_amount, NewAmount) as amount_prorated

Resident Table;


DROP Table Table;

andonialbisu
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

Sorry for the inconvenience and thanks for your answer.

Andoni

Re: Prorate sale amount

Try this

Table:

LOAD key,

client,

amount;

LOAD * INLINE [

    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)

LOAD key,

Sum(amount/2) as NewAmount1

Resident Table

Where client <> 'A'

Group By key;


Left Join (Table)

LOAD key,

Sum(amount/2) as NewAmount2

Resident Table

Where client = 'A'

Group By key;


FinalTable:

LOAD key,

client,

amount,

If(client = 'A', RangeSum(amount, NewAmount1), If(IsNull(NewAmount2), amount, amount/2)) as amount_prorated

Resident Table;


DROP Table Table;