topic Re: Combine unique value and description column from two different tables in App Development

Combine unique value and description column from two different tables

Bart_Breekveldt — Sat, 16 Nov 2024 06:01:44 GMT

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.

Re: Combine unique value and description column from two different tables

miskinmaz — Tue, 23 Apr 2019 10:21:29 GMT

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

Re: Combine unique value and description column from two different tables

shiveshsingh — Tue, 23 Apr 2019 10:30:04 GMT

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

Re: Combine unique value and description column from two different tables

Bart_Breekveldt — Wed, 24 Apr 2019 08:27:32 GMT

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

Re: Combine unique value and description column from two different tables

Bart_Breekveldt — Wed, 24 Apr 2019 08:56:24 GMT

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.

Re: Combine unique value and description column from two different tables

shiveshsingh — Thu, 25 Apr 2019 10:10:42 GMT

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.

Re: Combine unique value and description column from two different tables

Bart_Breekveldt — Thu, 25 Apr 2019 11:28:24 GMT

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

Re: Combine unique value and description column from two different tables

shiveshsingh — Thu, 25 Apr 2019 12:01:55 GMT

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;