I would concatenate the tables instead of joining them, and then use sum(Quantity) to get the sum of the two tables. But if you really want to join them, you're going to need to rename the Quantity field temporarily, because otherwise it will try to join on it and fail, and you'll get a concatenation anyway. And if the join doesn't fail, then you'll have the wrong quantity (half as much as you wanted).
You can do the join like this, if you really want to do that instead of concatenate:
EDIT: That first left join should probably be an outer join.
[Translate to French]: MAPPING LOAD * INLINE [ From, To Toys, Jouet Gift, Cadeaux Decoration, Décoration ]; [Data]: LOAD applymap('Translate to French',Item) as Item ,Quantity as Quantity1 INLINE [ Item, Quantity Jouet, 5 Cadeaux, 10 Décoration, 15 ]; LEFT JOIN ([Data]) LOAD applymap('Translate to French',Item) as Item ,Quantity as Quantity2 INLINE [ Item, Quantity Toys, 20 Gift, 25 Decoration, 30 ]; LEFT JOIN ([Data]) LOAD Item ,Quantity1 + Quantity2 as Quantity RESIDENT [Data] ; DROP FIELDS Quantity1 ,Quantity2 ;
FInally, I was able to make it with your first reply (mapping). I did the mapping and in the same table I load the second Excel file, so it matched to the other colum 🙂
Now I have a new problem...
I have an excel file with some names in the format : XXXXX, YYYYY and I want to load it in Qlikview in the format: XXXXX YYYYY
Example: I have :
Name
Bob, Bob
Abc, def
Xyz, Utv
and I nedd :
Name
Bob Bob
Abc def
Xyz Utv
Is it possible to remove this "," which is disturbing me ????
That's what I tried first but it was removing the ',' and doesn't put a space between the first name and the last name...
Finally I found out this :
replace (Name, ',' , ' ') as Name
and it works 🙂
But still a small problem, some names got an accent like Michèle in one file and not accent Michele in the other file, is it also possible to remove the accent?
Your example data showed a space after the comma, which is why I gave the solution I did. Anyway, looks like you have it solved now.
As for making the names match, I often use a map to help with data clean up and presentation:
[Text Map]: LOAD * INLINE [ From, To Mcdonald,McDonald Michèle, Michele Usa, USA ... ];
mapsubstring('Text Map',Field) as Field
Actually, our source data is often in all caps, and sometimes unreliably so, so more typically I do this:
mapsubstring('Text Map',capitalize(Field)) as Field
If you want to replace ANY accent marks, you could just list all of the accented characters and their unaccented equivalent in the text map, as opposed to specific names and words you want to fix.
