concatenation - qlik sense - concat the same column few times

Question

I have to do this exercise and I need your help.

I have a table with this rows: Table Emp: Id, Name, Country, Age. I have loaded this table twice

The second table is Table Emp2: Id2, Name2, Country2, Age2. (with the same values like table emp1)

I want to create a pivot table, the dimension will be emp.id. the measure will be the concatinate of all the id's of the employees in table emp2 that have the same name and country.

Do I need to use the concat function? and how to do it?

Thank you!

question from:https://stackoverflow.com/questions/65599768/qlik-sense-concat-the-same-column-few-times

Answer 1

Probably its easier to solve this in the script first.

(looking at the script below) We can see that there is one match - from Employee table the row with Id = 1 is matching with Employee2 rows Id2 = 1 and Id2 = 2

To get the match we can create additional table Employee_Link which will contain the link between Id and Id2.

To form the table we are loading (from Employee table) Id and concatenating Country and Name fields to form new field NameCountryLink (im using AutoNumberHash128 function to get number representation of the concatenated string)

The resulted table we are left join to the same table structure but sourced from Employee2 table.

The final table will contain only the matches between Employee and Employee2 tables.

Once we have the data we can create the UI table with dimension Id and measure concat(distinct IdMeasure, ',')

And the result will be:

As you can see only the first row contains the concatenated values and there is no match for Id = 2

Employee:
Load * Inline [
Id, Name , Country , Age
1 , Adam , Country1, 20
2 , John , Country2, 21
3 , Ana  , Country3, 22
4 , Rose , Country4, 23
5 , David, Country5, 24
];

Employee2:
Load * Inline [
Id2, Name2, Country2, Age2
1  , Adam , Country1, 20
2  , Adam , Country1, 21
3  , Ana  , Country3, 22
4  , Rose , Country4, 23
5  , David, Country5, 24
];

Employee_Link:
Load
  Id,
  AutoNumberHash128(Name, Country) as NameCountryLink
Resident
  Employee
;

left join

Load
  Id2                                as IdMeasure,
  AutoNumberHash128(Name2, Country2) as NameCountryLink
Resident
  Employee2
;

// Optional - we can drop NameCountryLink if we dont need it anymore
// Drop Field NameCountryLink;