topic Re: unique ids from multiple reporting periods list in QlikView

unique ids from multiple reporting periods list

Thu, 31 May 2012 11:38:16 GMT

Hi There,

I'm relatively new to Qlikview and have searched various forums but not totally sure how to approach this issue.

I have a table that contains a list of calloffs with reporting periods:

I need to load a list containing unique calloff reference with the most recent reporting period date preferably in a unqualified table.

Could someone show me an example of how to do this?

Thanks for your help!

unique ids from multiple reporting periods list

flipside — Thu, 31 May 2012 12:03:27 GMT

Hi Adam,

You can do this quite easily with a GROUP BY statement ...

Grouped:

LOAD [Call-Off Reference], max([Reporting Period]) as LatestPeriod

resident {sourcetable}

group by [Call-Off Reference];

This will link the LatestPeriod to the Call-Off Reference, but if you want to have it completely unlinked, change to something like ..

Grouped:

LOAD [Call-Off Reference] as grp_Ref, max([Reporting Period]) as LatestPeriod

resident SampleData

group by [Call-Off Reference];

flipside

unique ids from multiple reporting periods list

Thu, 31 May 2012 14:53:39 GMT

Hi,

So thats nearly there but how would I then display all the details of each most recent call off record in a table?

Thanks!

Re: unique ids from multiple reporting periods list

Thu, 07 Jun 2012 09:30:28 GMT

Ok so i have this code:

LOAD

[Active],

[Call-Off Reference],

max(date([Reporting Period])) as LatestPeriod

resident Main_Table

group by [Call-Off Reference];

it throws an invalid expression.. whats with that?

I try and do a left join instead so it shows the data from the latest reporting periods only with unique call offs.

////////////////////////////////////////////////////////////

QUALIFY*;

UNQUALIFY [Call-Off Reference], [Reporting Period];

final_data:

LOAD

[Call-Off Reference],

max(date([Reporting Period])) as LatestPeriod

resident Main_Table

group by [Call-Off Reference];

LEFT JOIN LOAD

[Call-Off Reference],

[Call Off Title]

resident Main_Table;

UNQUALIFY *;

and it just reprints alot.. any ideas?

Re: unique ids from multiple reporting periods list

flipside — Mon, 02 Jul 2012 08:41:13 GMT

Hi,

Sorry, missed your new comments. You might have solved this already, but this is one way how it could be done ...

SampleData:
LOAD * INLINE [
Call-Off Reference, Reporting Period, Stage
COR007977, 01/01/2011, desc A
COR007977, 01/02/2011, desc B
COR007977, 01/03/2011, desc B
COR007977, 01/04/2011, desc A
COR007977, 01/05/2011, desc C
COR007978, 01/03/2011, desc C
COR007979, 01/03/2011, desc Q
COR007979, 01/03/2010, desc A
COR007979, 01/08/2009, desc B
COR007979, 01/03/2006, desc C
COR007975, 01/01/2011, desc A
COR007975, 01/07/2011, desc D
COR007975, 23/08/2011, desc Z
COR007975, 01/02/2011, desc B];

LEFT JOIN (SampleData)
LOAD *, RowNo() as RowRef resident SampleData; // this enforces row uniqueness for linking later

LEFT JOIN (SampleData)
LOAD [Call-Off Reference], max([Reporting Period]) as LatestPeriod // this finds latest date within each Call-off Reference group
resident SampleData group by [Call-Off Reference];

LEFT JOIN (SampleData)
LOAD RowRef, Match(num([Reporting Period]),num(LatestPeriod)) as LatestPeriodFlag
resident SampleData;

Hope this helps

flipside