Combine several Excel files (Association de plusieurs fichiers excel)

[#1]

Hello everyone,

I need you to solve a concern that I found during loading of data from 2 Excel files.

In fact I wanted to link two excel files by a column (like I'm going to do a join by that column that represents a reference), I managed to link the two files, you only have to give the same name to two columns when I load my data, that shows me the tables schema in QlikView there is a link between the two tables, until then it's good. For example for the first file I: the column that contains these reference values: 1,2,3,4,5,6 and the second contains files that: 1,4,5,7,8,9.

After loading, you can see a single column reference that is the concatenation of two field values, but I noticed that there are duplicates. the result is as follows: 1,1,2,3,4,5,6,7,8,9.

the problem is that the data for the 2 files after a selection of the reference 1, select the 2 lines that contains the value 1, if the data from a single file to be displayed.

I tried even with & LEFT JOIN RIGHT JOIN, but always the same concern, the files are not related.

Thank you for helping me solve this.

Translated with Google Translate - Qlik Community Administrative Team

Bonjour à tous,

J'ai besoin de votre afin de résoudre un souci que je l'ai trouvé pendant le chargement des données provenant des 2 fichiers excel.

En effet je voulais lier 2 fichiers excel par une colonne ( comme si je vais faire une jointure par cette colonne qui représente une référence), j'ai réussi à lier les 2 fichiers, il suffit de donner le même nom aux 2 colonnes, quand je fais load de mes données, ça m'affiche au schéma des tables dans QlikView qu'il existe une liaison entre les 2  tables, jusqu'au là c'est bon. Par exemple pour le 1er fichier j'ai :  la colonne référence qui contient  ces valeurs : 1,2,3,4,5,6 et le 2ème fichiers qui contient : 1,4,5,7,8,9.

Après le chargement, on peut voir une seule colonne référence qui fait la concaténation des valeurs des 2 champs, mais j'ai remarqué qu'il existe des doublons. le résultat est comme suit : 1,1,2,3,4,5,6,7,8,9.

le problème c'est que pour avoir les données des 2 fichiers après une sélection du référence 1, il faut sélectionner les 2 lignes qui contient la valeur 1, sinon les données d'un seul fichier qui seront affichées.

J'ai essayé même avec LEFT JOIN & RIGHT JOIN, mais toujours le même souci, les fichiers ne sont pas liés.

Merci de m'aider à résoudre ce souci.

[Gysbert_Wassenaar]

the problem is that the data for the 2 files after a selection of the reference 1, select the 2 lines that contains the value 1, if the data from a single file to be displayed.

I'm afraid this bit got a bit lost in translation. Can you explain, or even better show, the result you get and the result you expect?

[#3]

Hi Gysbert,

I will explain in english, i went to associate 2 files excel by a column, it's like a made a join between 2 tables in database by a primary key. so to do that i put the seem name for a column in the 2 excel files.

when I loaded data it's OK,I can see that there is a link between the 2 files. and I have also a column that is concatenation of the 2 column of the files.

for the first file I have for exemple a column reference that contains this values :   1,2,3,4,5,6

and for the second files,  I have a column id with this values : 1,2,3,7,8

before loading data: I change my script :  I make the seem name of column

for exemple  id as reference.

in the result :I have a column reference with this values : 1,2,2,3,3,4,5,6,7,8

if I select the first 2, I can see only the data from the first files, and to show all informations for the reference 2 in the 2 files, we must select the 2 lines (the first and the second 2).

What I need is make something like this : a column reference but without duplicate : 1,2,3,4,5,6,7,8 and when I select 2 I can see the informations from the 2 files not just one.

I try with LEFT JOIN , but it not works.

Thank you for your help.

[Anonymous]

If the value '2' is the same in both files, it will work exactly as you describe.  I suspect that the values are different, even if they look the same.  For example, in one table '2' is a number, in another table '2' is a string.  To make it work, you have to make sure they data are numbers (or strings) in both cases.

I think this will help you if you use it in both tables:

num(id) as id

[#5]

Thank you Michael, I will try your solution

[#6]

Hello Michael,

I change the type of the columns, I make Text for the 2 files.  I tried but it doesn't work .

Do you have any idea, I'm really blocked in this issue.

Thank you for your help

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem together with the source files you used?

[puttemans]

Bonjour Sihaam,

Maintenant, vous renommez votre variable afin de créer le lien. Ce que vous pourriez essayer, c'est de prendre la valeur dans le clé aussi. Donc ça deviendrait 'id&valeur' comme colonne. En qliquant sur id2, vous aurez les 2 valeurs qui correspondent dans les 2 tables.

Cdt,

Johan

[#9]

for the first file : I have :

UR WEB IDENTIFIER

310988D

310986D

310979D

303605D

303605D

009608A

And for the second file, I have :

Tache

117644B - Impact analysis

310986 - Impact analysis

310988 - Impact analysis

118105 - Impact analysis

117644 - Impact analysis

015489 - Impact analysis

310979 - Impact analysis

And this my script before making a link between the 2 files by this columns.

After loading I have the 2 columns, if I choose 0096088A I will show all informations for the 2 files.

Now, to associate the 2 files, I make the seem name, and I have in result one column :

You must select the 2 lines 009608A to show all informations for the 2 files, else we can show just the information for one file.

Thank you for you help

[Gysbert_Wassenaar]

310988D

310986D

310979D

303605D

303605D

009608A

117644B - Impact analysis

310986 - Impact analysis

310988 - Impact analysis

118105 - Impact analysis

117644 - Impact analysis

015489 - Impact analysis

310979 - Impact analysis

Those two sets have no values in common. None.