Based on join table by ID, create a column (1/0) when match / not mach

[joancasellasvega]

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';

So I’m creating the table ES.

Now, in the table ES, I would like to create a column with match/no match, based on another table (called MATCH), which might, might not have the same INSTANCIAS id. See table:

MATCH:

LOAD

"Ticket Id",
INSTANCIA,
FECHA_CREACION_INSTANCIA,
ESTADO

FROM [lib://Ficheros Qlik/match.xlsx]
(ooxml, embedded labels, table is [Table 1]);

So the MATCH table is telling me which INSTANCIAS I should focus on.

Base on the filter that I have in mind in the ES, I should be able to filter by those INSTANCIAS that match with the table MATCH and analysis the data in the ES table. I don't need any other information from the table MATCH.

Does that make sense? It this possible?

Thanks in advance,

Joan

[whiteymcaces · Accepted solution]

Load the MATCH Table first and add a field:

INSTANCIA as Temp_Match

Then in your other Load statements add the following as an extra field:

If(Exists(Temp_Match, INSTANCIA), 'Match', 'No Match') as FocusField

[jonathandienst]

You can do that as a join or as a mapping table:

For the join, load the match table like this:

Left Join (ES)
LOAD
	"Ticket Id",
	INSTANCIA,
	FECHA_CREACION_INSTANCIA,
	ESTADO,
	1 AS FocusFlag
FROM [lib://Ficheros Qlik/match.xlsx]
(ooxml, embedded labels, table is [Table 1]);

Now you have a field (FocusFlag) that you can use to select your focus items in the user interface or in set expressions.