Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an data which contains multiple dates but i have to load only latest date data in to the qlikview.
Name | Order date | Price |
Surya | 11/3/2016 | 100 |
Sagar | 10/3/2016 | 100 |
Siddhesh | 9/3/2016 | 100 |
Jay | 8/3/2016 | 100 |
Surya | 7/3/2016 | 100 |
Sagar | 6/3/2016 | 100 |
from above data i have to load first row data for surya as it is latest date in qlikview. i have tried MAX function but its not working.
Thanks,
Suryakant
...
NoConcatenate
Output:
LOAD
Name,
max("Order Date") as Date,
FirstSortedValue(Price,-"Order Date") as Price
Resident YourTable
Group by Name;
DROP Table YourTable;
But you have to ensure, that there's only one "firstsorted value" per date within the dimension name...
Hope this is what you are looking for:
Source_Data:
LOAD * INLINE [
Name, Orderdate, Price
Surya, 11/3/2016, 100
Sagar, 10/3/2016, 100
Siddhesh, 9/3/2016, 100
Jay, 8/3/2016, 100
Surya, 7/3/2016, 100
Sagar, 6/3/2016, 100
];
NoConcatenate
TMP:
LOAD max(Orderdate) as max_Orderdate
Resident Source_Data;
Let vMax = peek('max_Orderdate',0,'TMP');
Drop table TMP;
NoConcatenate
Final_Data:
Load *
Resident Source_Data
Where Orderdate=$(vMax);
Drop Table Source_Data;
Hi,
You can get the date as follows (in the script):
Temp:
Load
//min(date) as minDate,
max(date) as maxDate
Resident TABLE;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
I hope the answer will be useful!