Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm loading two tables with Concatenate, where INSTANCIA is the unique Identifier.
ES:
LOAD
"Ticket Id",
INSTANCIA,
FECHA_CREACION_INSTANCIA,
ESTADO,
.
.
.
LastModified,
Grupo,
Agente
FROM [lib://Ficheros Qlik/output 2020_05_19.xlsx]
(ooxml, embedded labels, table is [Table 1])
Where LastModified='19/05/2020';
Concatenate
LOAD
"Ticket Id",
INSTANCIA,
FECHA_CREACION_INSTANCIA,
ESTADO,
.
.
.
LastModified,
Grupo,
Agente
FROM [lib://Ficheros Qlik/output 2020_05_20.xlsx]
(ooxml, embedded labels, table is [Table 1])
Where LastModified='20/05/2020';
Every data I append a new set of data. The unique identifier is the INSTANCIA, but an INSTANCIA can be manage in two different days. Therefore I found duplicates INSTANCIA’s with different LastModified date.
I would like to have a filter to keep unique INSTANCE base on the latest LastModified date. Those that make sense. I don’t want to delete duplicates, but be able to filter on a table.
Any help will be really appreciate it.
Thanks in advance,
Joan
Hello Joan,
I don't know if it is that simple but.. what if you create a column in both loads with the
Instancia & '-'& Fecha_creacion_instancia ?
Or use rowno() to also concatenate with your instancia?
Thanks for your replay @Gui_Approbato.
Below I have highlighted what the solution should be:
Ticket Id | INSTANCIA | LastModified | Duplicate |
61951 | 401574183 | 26/05/2020 | 0 |
1157 | 401574183 | 27/05/2020 | 1 |
61952 | 401574394 | 26/05/2020 | 1 |
53897 | 401574427 | 26/05/2020 | 1 |
73678 | 401574530 | 26/05/2020 | 1 |
61953 | 401574548 | 26/05/2020 | 1 |
61954 | 401574603 | 22/05/2020 | 0 |
61955 | 401574603 | 24/05/2020 | 1 |
61956 | 401574873 | 26/05/2020 | 1 |
61957 | 401574892 | 26/05/2020 | 1 |
I would like to have a filter called duplicates, so I can show the INSTANCIAS which were last modify, based on the column LastModified.
thanks,
Joan
There are other ways to get the same result, and one I can think of is creating this key in your main table using INSTANCIA&LastModified , then you load only this column and count how many times it repeats..
So basically you would have:
ES:
Load (all fields),
INSTANCIA&LastModified as Key from Source;
Load distinct INSTANCIA&LastModified as Key,
INSTANCIA,
Count(INSTANCIA) as Duplicate Resident ES;
With this you will link both tables by the Key column and will have your Duplicate filter (but equals to or higher than 1).
Hi ,
I'm trying to upload the data following your script. For each day (i have multiple tables) I'm using:
ES:
Load (all fields),
INSTANCIA&LastModified as Key from Source1;
Concatenate
Load (all fields),
INSTANCIA&LastModified as Key from Source2;
Concatenate
Load (all fields),
INSTANCIA&LastModified as Key from Source3;
Concatenate
.
.
.
When adding ….
Load distinct INSTANCIA&LastModified as Key,
INSTANCIA,
Count(INSTANCIA) as Duplicate Resident ES;
… at the end of the editor Qlik is throwing me an error. Any thoughts?
Thanks,
Joan
Oh, I think we missed the 'group by' clause in the last table..
So the last table would be
Load distinct INSTANCIA&LastModified as Key,
INSTANCIA,
Count(INSTANCIA) as Duplicate Resident ES Group by INSTANCIA, LastModified;
Try this
I have attached an excel file with three tabs.
I would like to upload the three tabs as ES:
and have a column that filter those unique by the latest LASTMODIFY date.
I hope this can help
Thanks in advance,
Joan
Great, thanks for the file.
I don't know why this error shows up (if someone there knows please comment here), but I made a filter to solve this issue in the dashboard.
I believe it is what you need. Check this file and let me know.
Best,
hey @Gui_Approbato thanks for your rapid response.
I think we are almost there 😀
I have attached another file, also with the expected result. Hope that help too.
I want to keep in a table only instances with the latest modified date.
thanks in advance
Hi,
to identify duplicates based on date, I'm using the following scritp:
FinalTable:
LOAD *,
If(INSTANCIA = Previous(INSTANCIA), 'dup', 'unique') as Flag
Resident ES
Order By INSTANCIA, LastModified desc;
DROP Table ES;
Thanks,
Joan