Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
Thank you
Glad it helped .