topic Re: PIVOT Table with multiple String values per Cell in App Development

PIVOT Table with multiple String values per Cell

Wimmer-Logistik — Tue, 30 Nov 2021 18:17:33 GMT

Hello,

do i have a chance to build from a datasource which looks like

Date	Department	Employee
8/8/2021	1	A
8/8/2021	1	B
9/8/2021	2	A
9/8/2021	2	C

a pivot table which looks like this:

	Department 1	Department 2
8/8/2021	A B
9/8/2021		A C

In words:
Two (or more) string values in below order, without adding a new dimension

What i found was concat Funktion, but this only glues values together without line breaks

Any ideas?

Re: PIVOT Table with multiple String values per Cell

Taoufiq_Zarra — Mon, 02 Aug 2021 19:53:49 GMT

@Wimmer-Logistik Maye be like :

Input: LOAD * INLINE [ Date , Department, Employee 8/8/2021, 1, A 8/8/2021, 1, B 9/8/2021, 2, A 9/8/2021, 2, C ]; DATA: Generic LOAD Date as D1, 'Department '&Department as D2, concat(Employee,' ') as E1 resident Input group by Date,Department; CombinedGenericTable: Load distinct Date as D1 resident Input; drop table Input; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); //trace $(i) - '$(vTable)'; IF WildMatch('$(vTable)', 'DATA.*') THEN LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

output:

Re: PIVOT Table with multiple String values per Cell

Wimmer-Logistik — Tue, 03 Aug 2021 07:33:07 GMT

Hi,

thanks for your reply and your idea.

This is what i can already achive by using a pivot table in frontend.

What i wanted to do is, to write the employees in seperate lines.

Not written next to each other...

Is it somehow possible in a pivot table?

Tried it with chr(10) and chr(13) as delimeter, but this won't work in a pivot.

Any other ideas?

bye

Markus