Need To Get Data Per Week Based on Ids

[#1]

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

[Gysbert_Wassenaar]

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;

[#3]

Hi,

please find the attached file

Niranjan M.

[#4]

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

[#5]

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

[nagaiank]

The attached solution gives the desired result.

[#7]

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

[nagaiank]

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.

[#9]

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