Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
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 |
HI Yaseen,
Use groub by to achive the result
a:
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
];
b:
NoConcatenate
load Name, Number, Address,date(Date#(trim(Updation_Date),'DD-MMM-YY')) as Updation_Date Resident a;
c:
LOAD date(max(Updation_Date)) as date,Address,Name Resident b Group by Address,Name;
drop Table a,b;
In Dimension,
Name ,Number and Updationdate and in
Expression
firstsortedVale(Adress,Updationdate)
Hope this helps
Thanks a ton Vishwa, its working.
I have doubt plz clear me what is the meaning of "-updationdate" this.
Regards,
yaseen
Hello,
I think we can use FirstSortedValue() in the scripting only not in the charts.
Regards,
yaseen
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
Hello,
I have checked it is not working.
we can use first sorted value in expressions also
see the example attached