Knock Off the Negative Value ....

[deepakk]

Hi,

I want to Knock off the Postive value with respect to Negative Value .. eg :

Product Date Amount

A 01/02/2010 100

A 05/02/2010 60

A 07/02/2010 -70

A 10/02/2010 100

A 20/02/2010 -90

Output:

Product Date Amount

A 01/02/2010 90

A 10/02/2010 10

The -70 knock of the above 60 value and 10 value from 100 hence we have A on 01/02/2010 as 90.

and -90 knocked off 100 and the remaiing value is 10

Let me know how we can achieve this.

Deepak

[Anonymous]

Hi Deepak,

a couple of weeks ago I faced a similar problem. So, I adopted my solution to your case. I just grouped the records by assigning a number to them.

The script can be enhanced to take group the days within a month and not within the whole time period.

Check out the script:

//get data

Data:

LOAD * INLINE [

Product, Date, Amount

A, 01.02.2010, 100

A, 05.02.2010, 60

A, 07.02.2010, -70

A, 10.02.2010, 100

A, 20.02.2010, -90

];

//create record groups column

Temp1:

load

Product as Prod1

,Date as Date1

,Amount as Amount1

,if(peek(Amount1)<0,

peek(RecordGroup1) + 1

,

if(isnull(peek(Amount1)),

1,

peek(RecordGroup1)

)

) as RecordGroup1

resident Data

order by Product,Date;

//sum amount by recgroups

Temp2:

load

Prod1 as Prod2

,RecordGroup1 as RecordGroup2

,sum(Amount1) as Amount2

resident Temp1

group by

Prod1,

RecordGroup1;

//join temp1 and temp2 with min(Date)

Final:

load

Prod2 as Prod3

,RecordGroup2 as RecordGroup3

,Amount2 as Amount3

resident Temp2;

inner join

load

Prod1 as Prod3

,min(Date1) as Date3

,RecordGroup1 as RecordGroup3

resident Temp1

group by Prod1,RecordGroup1;

[deepakk]

hi valera,

Thanks for the logic but there is a small problem in this.

If you the change the -70 to -20 in the data you will not get the correct output..

A 01.02.2010 100

A 05.02.2010 40

A 10.02.2010 10

Let me know if your view on this.

Deepak

[Anonymous]

Hi Deepak,

I guess I did not understand well the problem. The current implementation creates the groups of records, the group end is reached when a record with a negative number is met. So, after this record a new group starts. Finally, all the records inside the groups are summed.

You are saying the logic is different. Could you explain? I'll see what is possible to do.

Valera 🙂

[deepakk]

Hi valera,

The logic is little different. we need to knock off the negative value with the last positive value. and finally we need to display the Product,date and the remaing positive value.

eg: Suppos in our same case we have

A, 01.02.2010, 100

A, 05.02.2010, 60

A, 07.02.2010, -40

A, 10.02.2010, 100

A, 20.02.2010, -90

Then the ouput should be

A, 01.02.2010, 100

A, 05.02.2010, 20 (-40 knocked off 60 and the remining 20 we need to display)

A, 10.02.2010, 10 (-90 knocked off 100 and the remining 10 we need to display)

ANOTHER EXAMPLE :

A, 01.02.2010, 100

A, 05.02.2010, 60

A, 07.02.2010, -80

A, 07.02.2010, -30

A, 10.02.2010, 100

A, 20.02.2010, -90

THEN THE OUTPUT SHOULD BE

A, 01.02.2010, 50 ( -80 KNOCKED OFF 60 AND WE HAVE BALANCE -20. ( -20+ -30= -50) WHICH KNOCK OFF THE 100 AND WE HAVE 50 REMAINING)

A, 10.02.2010, 10

ITS LITTLE TRICKY BUT PLEASE LET ME KNOW IF YOU HAVE ANY QUERY ON THIS.

DEEPAK

[Anonymous · Accepted solution]

Hi Deepak,

I guess I found a solution for your problem.

I adopted the algorithm of "sinking" the negative values until they "dissolve". So, what I actually did was:

1) sorting the table by product asc, date desc

2) in the load script checking the previous newly generated column with the sum of amounts

- if the previous newly generated amount is negative, then sum with current amount

- otherwise, put the current amount.

These steps produce a temp table where I all the positive newly generated amounts are the values that you need. So, here I create the Final table with the positive values from the temp table.

What remains is how to get the negative values (if the total sum of the amount for a product is negative).

3) create a temporary table with total amounts

4) filter a table from the step 2) to leave only the absolute negative values

5) add these negative values to the Final table

The resulting script is the following:

qualify *;

//get data
Data:
LOAD * INLINE [
Product, Date, Amount
A, 01.02.2010, 100
A, 02.02.2010, 60
A, 03.02.2010, -20
A, 04.02.2010, 100
A, 05.02.2010, 60
A, 06.02.2010, -90
A, 07.02.2010, 100
A, 08.02.2010, -40
A, 09.02.2010, -70
B, 09.02.2010, 10
B, 10.02.2010, -70
];

//sink negative values until they disappear
Temp:
load
Data.Product as Product
,Data.Date as Date
,Data.Amount as Amount
,if(peek('Temp.Product') = Data.Product,
if(peek('Temp.Results') < 0,
Data.Amount + peek('Temp.Results'),
Data.Amount
),
Data.Amount
) as Results
resident Data
order by Data.Product asc,Data.Date desc;

unqualify *;

//load positive results
Final:
load
Temp.Product as Product
,Temp.Date as Date
,Temp.Results as Amount
resident Temp
where Temp.Results >= 0;

//get total amounts for products
NegativeProducts:
load
Temp.Product as Prod
,sum(Temp.Amount) as Res
resident Temp
group by Temp.Product;

//clear temp from products with positive total amount
inner join (Temp) load
Prod as Temp.Product
,Res as Temp.Results
resident NegativeProducts
where Res < 0;

//get rid of temporary table
drop table NegativeProducts;

//add remaining negative values to Final table
concatenate(Final) load
Temp.Product as Product
,Temp.Date as Date
,Temp.Results as Amount
resident Temp;

drop table Temp;

Find attached a Qv file solving the problem.

Best wishes,

Valera

[deepakk]

Superb Buddy !!! Looks Perfect ....!!! Thanks Thanks Thanks!!!!!

Let me try it in my Scenario and i will get back to you if i have any queries..!!!!

Thanks

Deepak