Dynamic pivotTable in fileOutputExcel resulting from data selection?

[Anonymous]

Hello,
I use a tFileOutputExcel component to collect data from a database for statistics purpose.
I need to declare a pivotTable selection to get a better view of selected data.
How can I do to get the benefit of a pre-configured pivotTable ("Tableau Croisé Dynamique") without needing to apply a new pivotTable anytime I need to refresh statistics results.
If I use a tFileCopy to retreive a kind of excelOutputFile template with preconfigured settings of a pivotTable in a sheet as output columns names are always fixed values, I got the selected data but the excelSheet containing the pivotTable is no longer active as a pivotTable but only contains fixed data.
How can I do to keep the dynamic pivotTable selection for further statistic purpose of a new data selection set?
Anyhelp would be appreciated.
Kind regards,
JNB

[Anonymous]

Hi!
A possible solution not involving OpenStudio, but just excel:
Create the Pivot-Table in a seperate Excel file (call it pivot.xls), referencing the data from your output excel file (output.xls).
In theory, each time you open pivot.xls, Excel will ask you if you want to update the data from the external output.xml file. Even if excel does not ask you, you should be able to simply right-click on the pivot table and choose to update the data.
As long as output.xls is always stored to the same location this should work. When creating the pivot table you can choose to also "embed" the data, so that pivot.xls can be used on another system that does not have output.xls.
Hope that helps.
Regards from Vienna,
Richard Unger

[Anonymous]

Hi,
thanks for your help, I think it could work. I'll let you know in a couple of days.
Bye.
JNB

[_AnonymousUser]

I have somehow the same problem:
I get bug csv files from an external application, and I would like to use Talend to create pivot Table. I can not use Excel since the file have too many lines.
Is there an easy way to do a pivot table in Talend Open Studio ?
Thanks in advance for your answer,

[Swechchha]

Had same issue. Did a bit more alongwith your so,ution and it WORKED for me 🙂

1. Create Sample Excel Workbook with Pivot sheet and a "data" sheet.
2. Select range of cells in your "data" on which you created your pivot and Name it using Name Manager in Menu.
3. Change formula of this Named Range in Name Manager to dynamic : =OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))
4. Right click on any cell in your PIVOT table sheet.
5. Click PivotTable Options and select Data Tab.
6. Add a check mark to Refresh Data When Opening the File. Click Ok.
7. Now in Your Talend Job, using tFileCopy , copy your Template in destination.
8. Using tFileOutputExcel overwrite your "data" Sheet simply how you do it always. (Append Existing File, not Existing Worksheet)(Is absolute Y pos)
9. Ta Da.😊