Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

unique ids from multiple reporting periods list

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:

help1.bmp

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!

4 Replies
Highlighted
Specialist II
Specialist II

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

Highlighted
Not applicable

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!

Highlighted
Not applicable

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?

Highlighted
Specialist II
Specialist II

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