topic Re: FirstSortedValue with string and date in QlikView

FirstSortedValue with string and date

Wed, 29 May 2013 03:15:12 GMT

Hi guys,

I have a table like the following:

Name	Service No	Date
abc	1	05/2013
xyz	2	05/2013
abc	1	04/2013
xyz	2	04/2013
abc	1	03/2013
xyz	2	03/2013
-	1	02/2013
xyz	2	02/2013
-	1	01/2013
-	2	01/2013

Now what i want is for any month to find the name accosiated with the service, or the most recent.

E.g. service 1 on 03/2013, i require it to return xyz

Which is easy.

But if no month is selected i require it to return the most recent.

e.g. Service 2 to return xyz

I could use maxstring, but i need it ordered by date not alaphabetically.

I've tried using firstsortedvalue(Name,Date) when it's filtered on a service but still no luck.

Any ideas?

Thanks so much in advance.

Re: FirstSortedValue with string and date

kumarnatarajan — Wed, 29 May 2013 05:02:49 GMT

Hi,

Try below mentioned script.

Ex:

LOAD ServiceNo,Date(Date#(Date,'MMM-YY'),'MMM YYYY') as Date,if(len(Name)=0,Peek(NewName),Name) as NewName;
LOAD * INLINE [
    Name, ServiceNo, Date
    abc, 1, May-13
    xyz, 2, May-13
    abc, 1, Apr-13
    xyz, 2, Apr-13
    abc, 1, Mar-13
    xyz, 2, Mar-13
    , 1, Feb-13
    xyz, 2, Feb-13
    , 1, Jan-13
    , 2, Jan-13
];

Find the attached file

Re: FirstSortedValue with string and date

Wed, 05 Jun 2013 05:45:12 GMT

Hi Kumar,

Thanks for your response.

This would work but all the data is dynamic, therefore a inline is not possible.

I just need to choose the name per service number with the greatest date.

Thanks

Re: FirstSortedValue with string and date

swuehl — Wed, 05 Jun 2013 07:45:49 GMT

FirstSortedValue() should work just fine.

You need a numeric Date, which I assume that's what you created (using QV date / time functions).

Then create a table chart with service number as dimension and as expression

=FirstSortedvalue(Name, -Date)

[Minus sign is important to get the most recent / highest date, not the lowest]

Re: FirstSortedValue with string and date

Wed, 05 Jun 2013 23:11:43 GMT

My Date is converted into just MM/YYYY.

So in my formula i wrote:

=FirstSortedValue(Name,-Num('01/'&[Date]))

Which still does not return any results.

Re: FirstSortedValue with string and date

Thu, 06 Jun 2013 01:00:10 GMT

Finally fixed it.

For anyone in future, there were two issues that I hadn't addressed.

Firstly there were more than one name per Date, so a Distinct in the FirstSortedValue was needed.

Secondly although i had a Date#(Date) in my initial load i didnt put what format the date was in.

So my ending results were, in my load statement

Date#(RIGHT([Date],7),'MM/YYYY') as [Date],

this was to return the month and year only, I could have achived this with the date format at the top but i bring other dates into my model also.

Secondly in the expression:

=FirstSortedValue(DISTINCT mob_ServiceOwner,-[Date])

Which returned the correct results, (although my qlikview doesn't recognise Distinct and puts the red line underneath to show error)