Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.LOAD Autonumber(Period & Name) AS KEY, * FROM yourtablepathhere
Hope this helps.
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
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.LOAD Autonumber(Period & Name) AS KEY, * FROM yourtablepathhere
Hope this helps.
Hi
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
Hi Gustavo,
here you go.
Good luck!
Rainer
tks guys! the attached qvw did the job for me! Once again, thanks!