How to display the IDs in a table which are not in the first table in Qlik Sense

[Gnawa]

Hello everyone,

I'm a new beginner in Qlik Sense.

I want to display in a new table the id which is not in the first table.

I can't use the function Inline because I have huge data tables to exploit.

Here are my two tables in the script:

Table1:

LOAD

CODE_SOCIETE,
CODE_AGENCE,
ID_CLIENT,
"id fact" ,
LIBELLE,
LIBELLE1,
LIBELLE2,
NOM_SOC,
VILLE1
FROM [lib://source.xlsx]

Table2:
LOAD
"Nom Commercial",
"Pays / Région",
"Type d'enregistrement",
"Ultimate Account",
"id fact",
"key expl"
FROM [lib://Source2.xlsx]

I want to create a table that shows me all the "id fact" that do not exist in the first table

I will be so happy if you help me, wish you all a happy new year.

[Frank_Hartmann · Accepted solution]

Something like that should work:

Table1:
LOAD
CODE_SOCIETE,
CODE_AGENCE,
ID_CLIENT,
"id fact" ,
LIBELLE,
LIBELLE1,
LIBELLE2,
NOM_SOC,
VILLE1
FROM [lib://source.xlsx]

Map:
Mapping LOAD ["id fact"], ["id fact"]
FROM [lib://source.xlsx]

Table2:
LOAD
"Nom Commercial",
"Pays / Région",
"Type d'enregistrement",
"Ultimate Account",
"id fact",
ApplyMap('Map', ["id fact"], '#') as Test,
"key expl"
FROM [lib://Source2.xlsx] where ApplyMap('Map', [ID Fact],'#')='#';

[rubenmarin]

Hi, if "id fact" is unique (only one row for each value) you can just add an Exists() clause:

FROM [lib://Source2.xlsx] where not Exists("id fact");

[Gnawa]

Hi Frank,

thank you for your suggestion, but it's doesn't work unfortunately

i have the error below :

L'erreur suivante s'est produite:

Field '"id fact"' not found

Emplacement de l'erreur:

Map: Mapping LOAD ["id fact"], ["id fact"] FROM [lib: // source.xlsx] (ooxml, embedded labels, table is ***)

[Gnawa]

Hi Ruben,

Thank you for your suggestion, but i can't see the result .

[Frank_Hartmann]

can you provide the 2 Excel files?

If there are sensitive data in it just replace them with some dummy data but do not change the attribute names.

[Gnawa]

Yes 

[Frank_Hartmann · Accepted solution]

Mapping_Table:
Mapping LOAD    
    "id fact", "id fact"   
FROM [lib://Source/Table1.xlsx]
(ooxml, embedded labels, table is Feuil1);

Table2:
LOAD
    "Nom Commercial Société",
    "Pays / Région PF",
    "Type d'enregistrement",
    "Ultimate Account",
    "id fact",
    ApplyMap('Mapping_Table', "id fact", '#') as Flag,
    "key expl"
FROM [lib://Source/Table2.xlsx]
(ooxml, embedded labels, table is Feuil1) where ApplyMap('Mapping_Table', "id fact",'#')='#';;

Maybe you will have to adapt the "FROM [lib://Source/Table*.xlsx]" according your environment.

If you execute the script only "Id fact" numbers of table 2 are shown which are not present in table 1

[Gnawa]

Thank you so much , it works !