topic Need to create a multi tab output with different columns in each tab in Talend Studio

Need to create a multi tab output with different columns in each tab

Chaitanyas — Sat, 13 May 2017 03:41:43 GMT

Hi,

First of all I want to thank Shong once again for helping me creating a multi-tab report based on a column.

I need solution to a similar kind of situation.

I have a requirement that data should be placed on different sheets based on a column and at the same time, output columns for each tab should be different.

Let me explain in detail. I have a single SQL which contains the logic for the both Summary and Detail. And I need to populate the excel file with two tabs one for Summary & other tab for detail and output columns should be different for each tab. I have a field called Flag which consist of values Summary and Detail. Based on this value, the tab need to be categorized and fields should be categorized.

Could anyone please help me in achieving in Talend.

Thanks in-advance!

Thanks,

Chaitanya

Re: Need to create a multi tab output with different columns in each tab

TRF — Sat, 13 May 2017 08:18:56 GMT

Hi,

After reading the value from the source, add a tFilter to separate "Summary" records ans "Detail" records.

Here is the tFilter I configured:

Push the filter flow to a tFileOutputExcel with the following configuration (see Sheetname and Append to an existing file options):

Push the reject flow to a tHashOutput cause you cannot write to 2 separated sheets in the same subjob (see this previous post https://community.talend.com/t5/Design-and-Development/creating-multiple-sheet-in-tFileOutputExcel/m-p/24250#M3834).

Start a new subjob connecting to the 1st one using the trigger OnSubjobOK.

Add a tHashInput associated to the previous tHashOutput with the same schema.

Copy/Paste the tFileOutputExcel and the Sheetname to "Detail".

Connect these components.

Here is the whole job.

Run and that's all.

Note: refer to this post for a solution aloowing you to address all the desired sheet in the same flow https://community.talend.com/t5/Design-and-Development/Create-Multiple-Tabs-with-single-excel-file-based-on-the-column/m-p/22817#M3118

Re: Need to create a multi tab output with different columns in each tab

Chaitanyas — Sat, 13 May 2017 12:52:04 GMT

Hi TRF,

Thanks for the solution provided.

But in my requirement I have 4 flag values 1 for Summary, 2 for Member Detail, 3 for Member PCP Detail and 4 for Provider Detail.

I'm not able to split these 4 values into 4 tabs with the different output columns. Could you please provide me few more sample screens to achieve this.

Thanks once again!

Thanks,

Chaitanya

Re: Need to create a multi tab output with different columns in each tab

TRF — Sat, 13 May 2017 14:31:26 GMT

You just have to follow the example, adding other 2 tFilterRow on reject flow to separate other flag values.
In your case you need 4 tHashOutput, 1 for each flag value.
You may also follow the response from your previous question if you don't have too much columns and if the schema is the same for each sheet
https://community.talend.com/t5/Design-and-Development/Create-Multiple-Tabs-with-single-excel-file-based-on-the-column/td-p/22746

Re: Need to create a multi tab output with different columns in each tab

Chaitanyas — Sat, 13 May 2017 14:42:43 GMT

That works!

Thank a ton!

Really I have few new things from all your responses for my questions.

Thanks once again!

Have a great Day!

Thanks,

Chaitanya

Re: Need to create a multi tab output with different columns in each tab

TRF — Sat, 13 May 2017 21:44:29 GMT

Refer to the following link to a better and more generic answer for this use case:

https://community.talend.com/t5/Design-and-Development/creating-multiple-sheet-in-tFileOutputExcel/m-p/24310#M3873

You will have the same result within a single subjob and as many sheets as needed.