Five Stars

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

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

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars TRF
Seven Stars

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

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-b...

TRF
5 REPLIES
Seven Stars TRF
Seven Stars

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

Hi,

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

Here is the tFilter I configured: 

Capture.PNG

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

Capture.PNG

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...).

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.

Capture.PNG

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-b...


TRF
Five Stars

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

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

Seven Stars TRF
Seven Stars

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

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-b...

TRF
Five Stars

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

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

Seven Stars TRF
Seven Stars

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

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...

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


TRF