topic Re: Latest Date Issue in QlikView

Latest Date Issue

Thu, 21 Feb 2013 05:59:52 GMT

Hi,

I want to load only rows which is having the latest Updation date.

Name	Number	Address	Updation Date
John	12	US	10-Jan-13
John	12	UK	15-Jan-13
Mary	14	UK	10-Jan-13
Wilson	15	India	15-Feb-13
Wilson	15	UAE	18-Feb-13
Martin	16	UAE	15-Feb-13
Mariya	17	China	18-Feb-13

In detail i want to load the Adress of employee based on the latest Updation Date.( For John Adress should be UK)

Thanks in Advance.

Re: Latest Date Issue

Thu, 21 Feb 2013 06:19:26 GMT

load name,

max(date(updationdate)) as maxdate,

FirstSortedValue(address,-updationdate) as newaddress

Group by name;

LOAD name,

number,

address,

updationdate

FROM

(ooxml, embedded labels, table is Sheet1);

then output like this--

name	newaddress	maxdate
mary	uk	1/10/2013
john	uk	1/15/2013
martin	uae	2/15/2013
mariya	china	2/18/2013
wilson	uae	2/18/2013

Re: Latest Date Issue

Thu, 21 Feb 2013 06:30:14 GMT

HI Yaseen,

Use groub by to achive the result

LOAD * INLINE [

Name, Number, Address, Updation_Date

John, 12, US, 10-Jan-13

John, 12, UK, 15-Jan-13

Mary, 14, UK, 10-Jan-13

Wilson, 15, India, 15-Feb-13

Wilson, 15, UAE, 18-Feb-13

Martin, 16, UAE, 15-Feb-13

Mariya, 17, China, 18-Feb-13

];

NoConcatenate

load Name, Number, Address,date(Date#(trim(Updation_Date),'DD-MMM-YY')) as Updation_Date Resident a;

LOAD date(max(Updation_Date)) as date,Address,Name Resident b Group by Address,Name;

drop Table a,b;

Re: Latest Date Issue

SunilChauhan — Thu, 21 Feb 2013 06:32:19 GMT

In Dimension,

Name ,Number and Updationdate and in

Expression

firstsortedVale(Adress,Updationdate)

Hope this helps

Re: Latest Date Issue

Thu, 21 Feb 2013 06:51:52 GMT

Thanks a ton Vishwa, its working.

I have doubt plz clear me what is the meaning of "-updationdate" this.

Regards,

yaseen

Re: Latest Date Issue

Thu, 21 Feb 2013 06:55:26 GMT

Hello,

I think we can use FirstSortedValue() in the scripting only not in the charts.

Regards,

yaseen

Re: Latest Date Issue

SunilChauhan — Thu, 21 Feb 2013 07:30:52 GMT

Hi,

i think we can use firstsortedvalue in expression.to check type firstsortedValue() in expression.

if it show blue color and popup arguments it means it should use in expression otherwise not.

Hope this helps

Re: Latest Date Issue

Thu, 21 Feb 2013 07:33:27 GMT

Hello,

I have checked it is not working.

Re: Latest Date Issue

SunilChauhan — Thu, 21 Feb 2013 14:02:11 GMT

we can use first sorted value in expressions also

see the example attached