create a filter function

[giozotik]

Hello,

I have such an issue in qlik sense.

Have a database wich contains lots of entries

person_id	last_name	first_name	birth_date	document_id	cross_date	direction
100245	green	anne	03/25/1990	3a4092	05/05/2015 15:15:15	in
100190	jose	lee	08/15/1965	2c1020	05/10/2015 10:10:10	out
101760	justin	white	01/27/1983	4a3035	03/12/2015 14:14:14	in
100190	jose	lee	08/15/1965	2c1020	06/11/2015 16:16:16	in
100245	green	anne	03/25/1990	3a4092	07/06/2015 17:17:17	out
100248	green	anne	03/25/1990	3a4092	08/08/2015 18:18:18	in
100256	green	anne	03/25/1990	3a4092	09/09/2015 19:19:19	out
…

Each person has a unique person_id, i want to create a filter which will corrects database.

If person_id is different but last_name, first_name and birth_date is same take minimal person_id.

For e.g

green anne has three different person_id 100245,100248,100256 after the filter, the table would look like this:

person_id	last_name	first_name	birth_date	document_id	cross_date	direction	filter
100245	green	anne	03/25/1990	3a4092	05/05/2015 15:15:15	in	-
100190	jose	lee	08/15/1965	2c1020	05/10/2015 10:10:10	out	-
101760	justin	white	01/27/1983	4a3035	03/12/2015 14:14:14	in	-
100190	jose	lee	08/15/1965	2c1020	06/11/2015 16:16:16	in	-
100245	green	anne	03/25/1990	3a4092	07/06/2015 17:17:17	out	-
100248	green	anne	03/25/1990	3a4092	08/08/2015 18:18:18	in	100245
100256	green	anne	03/25/1990	3a4092	09/09/2015 19:19:19	out	100245
…

thank you.

[ogster1974]

You have some dup person ids to clean out as well.

Something like

Select distinct min(person_id), min(cross_date), last_name, first_name, birth_date, document_id

From...

Group By last_name, first_name, birth_date, document_id

[felipedl · Accepted solution]

Hi gio, with the code bellow:

x:
LOAD person_id, 
     last_name, 
     first_name, 
     date#(birth_date,'MM/DD/YYYY') as birth_date, 
     document_id, 
     cross_date, 
     direction
FROM

(ooxml, embedded labels, table is Plan1);


left join (x)
Load
last_name,
first_name,
min(person_id) as new_person_id
Resident x
Group by last_name,first_name;


NoConcatenate
y:
Load
person_id,
last_name,
first_name,
birth_date,
document_id, 
    cross_date, 
    direction,
    new_person_id as filter
Resident x
order by person_id;


drop table x;

And the attached file, you could get something like this

[giozotik]

Thank you

[felipedl]

Glad it helped .