Applying rebate value once in set of transactions

[Anonymous]

Hi

We have a big set of transactions based every day and that's created as QVD's daily which has transaction id, amount, date, location purchased, payer, etc.

while we are preparing the profitability, at the end of the month we are manually calculating a number in a separate spreadsheet to repay once say ex for Feb16, for this payer, purchased at this location, this product, we give back $4 once for the month.

during the end of the month in the transaction file this $4 is added to every transaction  based on the condition - for Feb16, for this payer, purchased at this location, this product,

for calculating profitability

what is the best way to get the $4 for the overall set of transactions rather than subtracting in each transaction record. i want to apply(add or subtract) this $4 once in overall transaction set based on the selection criteria.

are there any analytical function that can do this job like sql.?

give an example

Month          Payer          Product          Location          Invoice     Rebate

Feb 16          111111      DIESEL          Moorabin          $44            $3.15

Feb 16          222222      DIESEL          Carnegie            $53           $4.1

Feb 16          222222      DIESEL         Berwick              $43.23      $1.0

Feb 16          111111      DIESEL          Berwick             $44             $3.2

Feb 16          111111      DIESEL          Moorabin          $72.3          $3.15

Feb 16          123456      DIESEL          Melbourne          $44           $0.9

Feb 16          111111      DIESEL          Melbourne          $72.3          $1.2

Feb 16          222222      DIESEL         Berwick              $71.25      $1.0

If you look at payer 111111 above, you can see he has purchased in multiple locations. now to the qn, although the transactions for Moorabin are done twice i want to apply Rebate only once which is subtracting or adding $3.15 at the end of the month for the profitability elsewhere.

how do i do this

Pls ask questions if you are not sure

[swuehl]

Does it matter which record gets the rebate? I guess, no, right?

But aggregating the data grouped by Month, Payer, Product, Location with a sum of invoice and only(Rebate) is not what you want, right? In other words, do you need the granularity of the single invoices?

[swuehl]

Here is an example how you can clear duplicate records based on the condition:

LOAD Month, Payer, Product, Location, Invoice,

           AutoNumberHash256(Month,Payer,Product,Location) as Key,

            If(not exists(Key, AutoNumberHash256(Month,Payer,Product,Location)), Rebate) as Rebate

INLINE [

Month,         Payer ,         Product,          Location,          Invoice,     Rebate

Feb 16,          111111,      DIESEL,          Moorabin,          $44,            $3.15

Feb 16,          222222,      DIESEL,          Carnegie,            $53,           $4.1

Feb 16,         222222,      DIESEL ,        Berwick  ,            $43.23,      $1.0

Feb 16,          111111,      DIESEL,          Berwick ,            $44  ,           $3.2

Feb 16,          111111,      DIESEL,          Moorabin ,         $72.3  ,        $3.15

Feb 16,          123456,      DIESEL,          Melbourne ,         $44   ,        $0.9

Feb 16,          111111,      DIESEL,          Melbourne ,         $72.3 ,         $1.2

Feb 16,          222222,      DIESEL ,        Berwick    ,          $71.25 ,     $1.0

];

[Anonymous]

Hi Stefan ,

Thanks for your email. that was helpful to start

I have attached the QVW with your example. if i set up a straight table, i can see the transaction record skipping ex $71.25 line is skipped.  The example you provided is very helpful i would think how i can fit this in my requirement.

The sum i get should be applied only once at any given time.

Is there a way i could get this in set analysis since i have already got this Rebate value into big transaction QVDs mapped to each transaction.

i have attached the sample transaction QVD where you can see the manual rebates across all the transactions.

[Anonymous]

Hi Stefan ,

I understood with what is happening with straight table but i have a situation to stick this rebate to each transaction and get only the distinct rebate to be applied for that combination.

[Saravanan_Desingh]

Very intelligent trick, Stefan

[Saravanan_Desingh]

Are you looking, something like this? I modified a bit of Stefan's version.

RawData:

LOAD *

INLINE [

Month,         Payer ,         Product,          Location,          Invoice,     Rebate

Feb 16,          111111,      DIESEL,          Moorabin,          $44,            $3.15

Feb 16,          222222,      DIESEL,          Carnegie,            $53,           $4.1

Feb 16,          222222,      DIESEL ,        Berwick  ,            $43.23,      $1.0

Feb 16,          111111,      DIESEL,          Berwick ,            $44  ,           $3.2

Feb 16,          111111,      DIESEL,          Moorabin ,         $72.3  ,        $3.15

Feb 16,          123456,      DIESEL,          Melbourne ,         $44   ,        $0.9

Feb 16,          111111,      DIESEL,          Melbourne ,         $72.3 ,         $1.2

Feb 16,          222222,      DIESEL ,        Berwick    ,          $71.25 ,     $1.0

];

Data:

LOAD Month, Payer, Product, Location, Invoice,

     AutoNumberHash256(Month,Payer,Product,Location) as Key,

     If(Not Exists(Key, AutoNumberHash256(Month,Payer,Product,Location)), Rebate, Previous(Rebate)) as Rebate

     Resident RawData

Order By Month, Payer, Product, Location

;

Drop Table RawData;

Output:

Month	Payer	Product	Location	Invoice	Key	Rebate
Feb 16	111111	DIESEL	Berwick	$44	1	$3.2
Feb 16	111111	DIESEL	Melbourne	$72.3	2	$1.2
Feb 16	111111	DIESEL	Moorabin	$44	3	$3.15
Feb 16	111111	DIESEL	Moorabin	$72.3	3	$3.15
Feb 16	123456	DIESEL	Melbourne	$44	4	$0.9
Feb 16	222222	DIESEL	Berwick	$43.23	5	$1.0
Feb 16	222222	DIESEL	Berwick	$71.25	5	$1.0
Feb 16	222222	DIESEL	Carnegie	$53	6	$4.1

[swuehl · Accepted solution]

If you are looking to a front end solution, you could use advanced aggregation to create a virtual table with the distinct rebates per your combination of dimension values, like

=Sum(Aggr( Only(RebateOrig), Month,Location,Payer,Product))

You can use this expression in any chart with a dimension like Month or Product to see the Rebate aggregated correctly per dimension.

See attached sample.

I personally would prefer a solution in the script. You might consider also separating the rebates from your fact into another table linked by the common key.

I am attaching both solutions as sample QVW.

[Anonymous]

Hi Stefan ,

I agree with you that that the rebate should have been controlled within the script. I am working on that solution but set analysis would be a plan B if the QVD rebuild takes a pretty long time becas of the bulky data

takes 32 hours in my crapy test environment to rebuild the QVDs.

I accept your answers thanks very much