Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Gnawa
Contributor
Contributor

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

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.

 

 

2 Solutions

Accepted Solutions
Frank_Hartmann
Master II
Master II

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],'#')='#';

 

View solution in original post

Frank_Hartmann
Master II
Master II

 

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

View solution in original post

8 Replies
Frank_Hartmann
Master II
Master II

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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

Hi Ruben,

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

Frank_Hartmann
Master II
Master II

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
Contributor
Contributor
Author

Yes 

Frank_Hartmann
Master II
Master II

 

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
Contributor
Contributor
Author

Thank you so much , it works !