topic Combine values of different columns in same rows in App Development

Combine values of different columns in same rows

TinaH — Tue, 19 Apr 2022 07:42:20 GMT

Hello,

unfortunately I can not progress with the following topic. Starting point is the app provided attached. In this application I want to display which employee has one of the roles (e.g. 2nd level support) for the upcoming 3 calendar weeks.

I was able to import the data (see data script). However, I want the respective employees in the calendar weeks to be displayed next to each other and not with null values and below each other for the weeks.

Thus, the goal is the following representation:

Role	2022-16	2022-17	20202-18
2nd Level Support	Employee2	Employee3	Employee1
2nd Level Support	Employee12	Employee5	Employee7
2nd Level Support	-	-	Employee14
abwesend	Employee8	Employee12	Employee15
abwesend	Employee15	Employee15	-
Systemupdate-Code	Employee6	Employee14	Employee8
Systemupdate-SQL	Employee10	Employee2	Employee5

Has anyone got any ideas on this?

Thank you very much!

Re: Combine values of different columns in same rows

vchuprina — Tue, 19 Apr 2022 07:37:47 GMT

Hi,

Please also upload data.xlsx file

Regards,

Vitalii

Re: Combine values of different columns in same rows

TinaH — Tue, 19 Apr 2022 07:38:53 GMT

Sorry, please find attached the data.xlsx file

Re: Combine values of different columns in same rows

vinieme12 — Tue, 19 Apr 2022 07:51:44 GMT

CrossTable(Week,Role,1)
Raw:
LOAD
F2 as Employee,
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
"17",
"18",
"19",
"20",
"21",
"22",
"23",
"24",
"25",
"26",
"27",
"28",
"29",
"30",
"31",
"32",
"33",
"34",
"35",
"36",
"37",
"38",
"39",
"40",
"41",
"42",
"43",
"44",
"45",
"46",
"47",
"48",
"49",
"50",
"51"
FROM [lib://AttachedFiles/data.xlsx]
(ooxml, embedded labels, table is [2022]);

Left Join(Raw)
Load * Inline [
Role,RoleText
a,abwesend
f,2nd Level Support
c,Systemupdate - Code
s,Systemupdate - SQL
r,Release Manager
];

Row = RoleText

Colum = Week

Measure = Maxstring(Employee)

Re: Combine values of different columns in same rows

vchuprina — Tue, 19 Apr 2022 08:24:38 GMT

Hi,

Vineeth provided a correct solution, but instead of Maxstring(Employee) you should use Concat(Employee, ',') in this case you will not lose the data in the chart.

Please check the attached file.

Regards,

Vitalii

Re: Combine values of different columns in same rows

TinaH — Tue, 19 Apr 2022 10:53:58 GMT

Hello,

thank you very much. That looks much better. But how would I get the employees, e.g. for the role 2nd level as single rows and not with "," or the maxstring?

Best regards
Tina

Re: Combine values of different columns in same rows

vinieme12 — Tue, 19 Apr 2022 11:11:48 GMT

refer this post

https://community.qlik.com/t5/New-to-Qlik-Sense/Allow-more-than-3-lines-for-a-table-cell/td-p/1737413

Re: Combine values of different columns in same rows

TinaH — Tue, 19 Apr 2022 11:19:24 GMT

I don't understand it, since I am not using a Multi-KPI chart?

Thats how it should look like:

Role	2022-16	2022-17	20202-18
2nd Level Support	Employee2	Employee3	Employee1
2nd Level Support	Employee12	Employee5	Employee7
2nd Level Support	-	-

Best regards
Tina

Re: Combine values of different columns in same rows

vinieme12 — Tue, 19 Apr 2022 12:50:35 GMT

the goal of the hack to inject custom css code on the page to allow the table to have unlimited height(basically fit to content)

Even though you don't need a Multi KPi chart to be displayed on the dashboard; the multi-kpi chart itself allows you to add custom css via its properties panel

So just drag a multi-chart in you dashboard reduce its size and hide it in a corner of your sheet; and just use the css property of multi-kpi chart to inject the css code give in the above link

Re: Combine values of different columns in same rows

TinaH — Tue, 19 Apr 2022 13:12:06 GMT

Hi,

I have done that, but the employees are still next to each other instead of below each other (cf. attached file).

Best regards
Tina

Re: Combine values of different columns in same rows

vinieme12 — Tue, 19 Apr 2022 14:55:16 GMT

Expression Used in chart: concat( Stage,chr(10))

Copy paste the below snippet in the Multi_kpi >>Property Panel >> Appearence >> css

.qv-pt .cell .value{
max-height: none;
overflow:visble!important;
white-space:pre;
}

Output

Re: Combine values of different columns in same rows

vinieme12 — Wed, 20 Apr 2022 04:57:04 GMT

@TinaH

kindly mark a response as a solution and close this thread