Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
I have data in following format
ThreadId ProductId ChangeDate WeekYear
1066 10 2012-11-08 10:45:00 W1245
1066 15 2012-11-09 10:45:00 W1245
1066 11 2012-11-10 11:45:00 W1245
1066 20 2012-11-12 10:45:00 W1246
1067 31 2012-11-12 09:45:00 W1245
1067 32 2012-11-13 12:45:00 W1245
1068 40 2012-11-19 10:45:00 W1247
1068 41 2012-11-21 10:45:00 W1247
Its like we have so many thread ids in every week and so many assignment of product ids are being taken in those weeks and we have to find the latest product id coressponding to every thread in each week
I want to get the data in such a manner that I could get the latest Product id (based on date) for every thread and for each week.
OUTPUT like that :
ThreadId LatestProductId ChangeDate WeekYear
1066 11 2012-11-10 11:45:00 W1245
1066 20 2012-11-12 10:45:00 W1246
1067 32 2012-11-13 12:45:00 W1245
1068 41 2012-11-21 10:45:00 W1247
Its very urgent friends Kindly suggest suitable loading mechanism to do this .
Tribhuwan
You can use the firstsortedvalue in the charts:
LatestProductId: FirstSortedValue(ProductId,-ChangeDate)
LatestChangeDate: FirstSortedValue(ChangeDate,-ChangeDate)
See attached qvw.
If you need to do this in the script:
Load
ThreadId,
FirstSortedValue(ProductId,-ChangeDate) as LatestProductID,
FirstSortedValue(ChangeDate,-ChangeDate) as LatestChangeDate,
WeekYear
from ...sourcetable...
group by ThreadId,WeekYear;
Hi,
please find the attached file
Niranjan M.
Hi Niranajn
Thanks for your help.
But the logic you provide is not giving the first row of desired data.
I want latest product id for every week and for every thread.
Tribhuwan
Hi Gysbert<http://community.qlik.com/people/gwassenaar>
Kindly find the attached excel
first sheet contains input data and second sheet is the desired output.
I tried your logic in load script but could not get the desired output.
Tribhuwan
The attached solution gives the desired result.
Hi Krishnamoorthy
Your approach is giving data upto certain extent. I have attached an xls file in this thread which contains the sample data and also required output data.
Kindly apply your logic and see if it works for that.
I am getting only ThreadId,WeekYear but LastProductId and LastChangedDate null.
Tribhuwan
FirstSortedValue() function fails if there are more than one rows meeting the sort criterion. In order to handle this, I have modified the application to use RowNo() function for each row. This assumes that the data is sorted by changed_date field in your spreadsheet.
Hi Krishnamoorthy
Thanks for your help. This logic is giving the desired result.
Basically I have a history table which keeps record of every work done on any thread (no of rows corresponding to any thread id).
Its like
Thread1