topic Re: Need help with FirstSortedValue - Negative count upto latest positive value in QlikView

Need help with FirstSortedValue - Negative count upto latest positive value

sanadm19 — Tue, 17 May 2016 15:17:26 GMT

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!

Re: Need help with FirstSortedValue - Negative count upto latest positive value

swuehl — Tue, 17 May 2016 15:42:56 GMT

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

Re: Need help with FirstSortedValue - Negative count upto latest positive value

sanadm19 — Tue, 17 May 2016 19:45:44 GMT

‌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!

Re: Need help with FirstSortedValue - Negative count upto latest positive value

Saravanan_Desingh — Tue, 17 May 2016 20:31:10 GMT

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

Re: Need help with FirstSortedValue - Negative count upto latest positive value

sunny_talwar — Tue, 17 May 2016 20:53:11 GMT

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;

Re: Need help with FirstSortedValue - Negative count upto latest positive value

Saravanan_Desingh — Tue, 17 May 2016 20:56:07 GMT

Agreed, Sunny

Re: Need help with FirstSortedValue - Negative count upto latest positive value

maxgro — Tue, 17 May 2016 21:23:55 GMT

Maybe this espression in a 2 dims chart

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

Re: Need help with FirstSortedValue - Negative count upto latest positive value

sanadm19 — Wed, 18 May 2016 08:19:30 GMT

Perfect. Thanks!

Re: Need help with FirstSortedValue - Negative count upto latest positive value

sanadm19 — Wed, 18 May 2016 08:20:38 GMT

Thanks, Sunny. Same answer, works great!