Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

giozotik
New Contributor II

create a filter function

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.

1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: create a filter function

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

sample.png

4 Replies
ogster1974
Honored Contributor II

Re: create a filter function

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
Valued Contributor III

Re: create a filter function

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

sample.png

giozotik
New Contributor II

Re: create a filter function

Thank you

felipedl
Valued Contributor III

Re: create a filter function

Glad it helped .

Community Browser