Need help with FirstSortedValue - Negative count upto latest positive value

[sanadm19]

Hi,

I know this should be easy but I've spent hours and just not getting anywhere.

I have a table looks like this:

DATE	CLIENT	CURRENCY	AMOUNT
01/01/2016	ABC	USD	-250
10/01/2016	ABC	USD	-151
14/01/2016	ABC	USD	190
01/01/2016	ABC	EUR	185
27/01/2016	ABC	EUR	-25
10/01/2016	DEF	GBP	-100
30/01/2016	DEF	GBP	0

I need an expression to count the number of negative amounts from the starting date up to the date with the latest positive amount. So the result should look like:

ABC USD 2

ABC EUR 0

DEF GBP 1

I'm trying to use FirstSortedValue(DATE,if(AMOUNT>=0,AMOUNT))   but it isn't working.

Any help will be appreciated. sample qvw is attached.

Thanks!

[swuehl]

On a given date, you have multiple records per Client and Currency, some negative and some positive. How do you determine which record comes first?

Also, do you need the solution in the frontend or can it be solved in the script?

If latter, please provide a sample input file (potentially with added time stamps).

[sanadm19]

‌Hi,

That's the way the source file is. the objective is to obtain the first positive amount starting with the max date in descending order And then counting the negative amounts for a client currency grouping up to the date obtained earlier. I need this in the front end so that user can select date.

Appreciate your help on this!

[Saravanan_Desingh]

SET DateFormat='D/M/YYYY';
Data:
Load *, If(Previous(CLIENT)=CLIENT And CURRENCY=Previous(CURRENCY),
          if(Previous(AMOUNT)<0,AMOUNT<0,
          0)
          ,AMOUNT<0) As Flag;
LOAD * INLINE [
    DATE, CLIENT, CURRENCY, AMOUNT
    01/01/2016, ABC, USD, -250
    10/01/2016, ABC, USD, -151
    14/01/2016, ABC, USD, 190
    01/01/2016, ABC, EUR, 185
    27/01/2016, ABC, EUR, -25
    10/01/2016, DEF, GBP, -100
    30/01/2016, DEF, GBP, 0
];

PFA..

[sunny_talwar · Accepted solution]

Will still have to see how it goes with the actual data, but one modification i would propose is to sort the data in a resident load before using Peek/Previous. The reason I say this is because the sample provided here might be sorted in a desired format, whereas original data may differ from this which will end up giving incorrect output.

SET DateFormat='DD/MM/YYYY';

Data:

LOAD * INLINE [

    DATE, CLIENT, CURRENCY, AMOUNT

    01/01/2016, ABC, USD, -250

    10/01/2016, ABC, USD, -151

    14/01/2016, ABC, USD, 190

    01/01/2016, ABC, EUR, 185

    27/01/2016, ABC, EUR, -25

    10/01/2016, DEF, GBP, -100

    30/01/2016, DEF, GBP, 0

];

FinalData:

LOAD *,

  If(Previous(CLIENT) = CLIENT and CURRENCY = Previous(CURRENCY),

    If(Previous(AMOUNT)<0,AMOUNT<0, 0), AMOUNT<0) as Flag

Resident Data

Order By CLIENT, CURRENCY, DATE;

DROP Table Data;

[Saravanan_Desingh]

Agreed, Sunny

[maxgro]

Maybe this espression in a 2 dims chart

Index(Concat(if(AMOUNT>=0,'+','-'), '', DATE), '+')-1

[sanadm19]

Perfect. Thanks!

[sanadm19]

Thanks, Sunny. Same answer, works great!