Combine unique value and description column from two different tables

[Bart_Breekveldt]

Dear community,

We want to see the number of orders per recipe. I want to combine the recipe number and recipe description which are in different tables. When I combine these values I get multiple descriptions per recipe number. The descriptions differ by way of abbriviation (difference is not important). We however want to get unique values (per recipe number one matched description).

In MS Excel I use INDEX MATCH a lot and sometimes VLOOKUP to fix this. 

How can I match the unique recipenumber and an description in one record/column?

Thanks in advance.

[miskinmaz]

You can use the applymap function from the backend to bring the description against the recipe number

[shiveshsingh]

Can we have some sample data? or you can try applymap to map the column names from two different tables.

[Bart_Breekveldt]

Hi Shiveshsingh,

Is this the way of sharing a Qlik file? Can you open it? Or should I do it in a different way?

Bart

[Bart_Breekveldt]

Worksheet is in Dutch. ''Debiteuren'' = Debtors, ''Gegevens" = Data, "Beheer" = Control, "Jaar" = Year, "Organisatie" = Organization, "Inkoop" = Purchase, "Recept" = Recipe, "Omschrijving" = Description, "Aantal" = Amount. Don't hesitate to ask for some other translations.

[shiveshsingh]

Can i have two tables in excel format? and also the required output?

I am not having license as of now, so will require excel to build the logic.

[Bart_Breekveldt]

Above, an Excel example. The description only differs in punctuation, abbriviation etc.

[shiveshsingh]

May be this?

T:LOAD * INLINE [
Product, No
Cream and Cheese, 44
Cream & Cheese, 44
Salami, 50
Ham & Cheese, 51
Ham and Cheese, 51
Ham-Cheese, 51
Ham. Cheese, 51
Bacon & Eggs, 48
Bacon & Egg, 48
Bacon Eggs, 48
];
R:mapping LOAD * INLINE [

Product, Original
Cream and Cheese, Cream and Cheese
Cream & Cheese, Cream and Cheese
Salami, Salami
Ham & Cheese, Ham & Cheese
Ham and Cheese, Ham & Cheese
Ham-Cheese, Ham & Cheese
Ham. Cheese, Ham & Cheese
Bacon & Eggs, Bacon & Eggs
Bacon & Egg, Bacon & Eggs
Bacon Eggs, Bacon & Eggs
];

NoConcatenate
F:

load ApplyMap('R',Product) as Product, No
Resident T;

drop table T;