Output a Multiline Excel file with specific ordered values

AmIanALien0x3f — Fri, 15 Nov 2024 23:05:20 GMT

I'm using Talend Open Studio Data Integration and I'm looking to export data from a relational DB to an Excel output file with a specific format

To sum up the problem

My input data table

Date|Site|Sales

2022-02-28|place1|10

2022-02-28|place2|8

2022-02-28|place3|15

2022-03-01|place1|15

2022-03-01|place3|20

2022-03-01|place5|20

2022-03-02|place2|8

2022-03-02|place4|6

2022-03-02|place2|10

My Output Data should have this format and is generated every 2 days with sales of 3 last days:

Monday | Place1 | Place3 | Place6 | Place2

Sales | 10 | 15 | <empty> | 8

Thuesday| Place1 | Place5 | Place3 | Place2

Sales | 15 | 20 | 20 | <empty>

Wednesday| Place1 | Place5 | Place3 | Place2

Sales | 15 | 20 | 20 | <empty>

I have used a tJavaFlex to generate this types of lines :

But now I don't know how to use this lines to generate queries using date, place as parameters to insert and fill cells.

Maybe I'm doing it wrong because finally I'm starrting from the final format I want instead of starting to retrieve all data and merge/filter/order them with another formatted table to generate the wanted Excel. But I don't really know how to that too

It seems to have to deal with normalization/denormalization but I don't know how to deal with ordering column and agreggating multiple lines with different ordered columns

Thank you in advance

topic Output a Multiline Excel file with specific ordered values in Talend Studio

Output a Multiline Excel file with specific ordered values