topic Creating a unique record list in QlikView

Creating a unique record list

Fri, 08 Jan 2010 02:23:46 GMT

Hello to all

This is my first post, and I´m quite new to Qlikview. I got this problem at work, and i can´t figure out a solution.

In the file attached, there are two tables. One with the information I currently have, and the other with the information filtered the way i need.

As you can see, i have two different names with the same code (Vacant and Neil, both with code 1). What i need is to create a table with unique records. In case of a code with multiple names, i need to get the newest name (according to the period row). The resulting table that i need is shown in the attached file (by the right side). How can I do this within Qlikview?

I need a solution which doesn´t include macros, due to security reasons at my workplace.

Thanks in advance for the help!

Gustavo - Brazil - São Paulo

Creating a unique record list

Miguel_Angel_Baeyens — Fri, 08 Jan 2010 08:27:32 GMT

Hello Gustavo,

I'd use Autonumber function. With that function (among others, but this one is very simple and powerful), you will create a unique number to identify objects. It's a way to create, based on the information you actually have (your field values) something like a "key". Using your example, try something like

LOAD Autonumber(Period & Name) AS KEY, * FROM yourtablepathhere

AS the combination of both gives a unique, non redundant result, autonumber will create a unique number so you can easily identify that record using the same Autonumber function when retrieving information from another table with the very same field values.

Hope this helps.

Creating a unique record list

Fri, 08 Jan 2010 08:39:11 GMT

First get the max value for each code:
Load
Code,
max(Period) as Period
from file.xls group by Code;

Then join with the name for given Period/Code:
Left join load
Code,
Period,
Name
from file.xls;

You can also do it all in one step as well, like this:
Load
Code,
max(Period) as Period,
firstsortedvalue(Name, -Period) as Name
from file.xls;

Look in the helpfile for aggregation functions to get more info.

/Fredrik

Creating a unique record list

Fri, 08 Jan 2010 08:42:06 GMT

Hi Gustavo,

here you go.

Good luck!

Rainer

Creating a unique record list

Sun, 10 Jan 2010 21:56:54 GMT

tks guys! the attached qvw did the job for me! Once again, thanks!