topic Re: Need To Get Data Per Week Based on Ids in QlikView

Need To Get Data Per Week Based on Ids

Fri, 25 Jan 2013 11:57:07 GMT

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

Re: Need To Get Data Per Week Based on Ids

Gysbert_Wassenaar — Fri, 25 Jan 2013 12:14:18 GMT

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;

Re: Need To Get Data Per Week Based on Ids

Fri, 25 Jan 2013 12:35:10 GMT

Hi,

please find the attached file

Niranjan M.

Re: Need To Get Data Per Week Based on Ids

Fri, 25 Jan 2013 13:20:29 GMT

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

Re: Need To Get Data Per Week Based on Ids

Fri, 25 Jan 2013 14:14:33 GMT

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

Re: Need To Get Data Per Week Based on Ids

nagaiank — Fri, 25 Jan 2013 14:38:25 GMT

The attached solution gives the desired result.

Re: Need To Get Data Per Week Based on Ids

Fri, 25 Jan 2013 15:07:13 GMT

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

Re: Need To Get Data Per Week Based on Ids

nagaiank — Fri, 25 Jan 2013 15:45:17 GMT

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.

Re: Need To Get Data Per Week Based on Ids

Mon, 28 Jan 2013 10:27:21 GMT

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