Filed Containing File Name or part of file Name Final Output

[AdminYogesh]

Hello Community members,

I am working on the Final output. There, I have to create multiple tabs in Excel based on the Group field Name, which I have completed 
But the challenge I am facing is that if the Entire column should Contain Zero (0), we have to remove it from the final output, same for all tabs

I am attaching the sample images of Input and Output for understanding what exactly I wanted.

I would appreciate it if I could get any help from you guys!!

[nikolinamilincevic]

Hi,

I am writing a suggestion which I believe works in this case, and in the case all your data is not Null(). 

1. use Multi-Field Formula Tool to change all 0 to Null()

2. use Data-Cleansing Tool to remove all Null columns

3. if you want back the zero's, then use again Multi-field Formula Tool to change Null() back to 0.

Let me know if it helps :) 

[AdminYogesh]

@nikolinamilincevic  Thanks for the quick reply,
This was a sample workflow that I created for understanding. But in I real scenario before creating final output I am using Cross tab tool based on Group column, considering it like this After that I want to spilt group field data wise tabs in final output. which I created Example- Ground, sky, sun
After that, i am getting nulls in different tabs I want to remove that from the final output by using 
I am using option to spilt data ( I select output as Field Containing File Name or part of File Name )

[Pilsner]

Hello @AdminYogesh 

Thank you for your post, I've tried to create a workflow that results in the output as per your image. (Please note, my dummy data is not exactly the same as yours, as I only have 8 rows).

I decided to use a batch macro as each group (batch) will have different columns that need to be removed, because they contain no data. 

Inside the macro I basically pivot the table down into rows, then filter out any null (or 0) values, before pivoting the data back to its original tabular form. This then feeds into an output tool configured the same as the output tool in your image.

This resulted in the following output (3 tabs, this is just one of them).

By approaching the problem this way I am able to remove the null rows per group, whilst still outputting to the same file. I have attached a copy of the workflow below.

Happy to go into more detail if my working doesn't make sense. Please let me know how you get on. 

Regards - Pilsner

[AdminYogesh]

@Pilsner Thank you so much!! It's working as expected

[AdminYogesh]

@PilsnerCan you please shared updated verison of Workflow

[Pilsner]

Hello @AdminYogesh 

Here is an updated version of the workflow as per our messages. 

The container connected at the top is relevant to the most recent version, which accepts all new columns and converts them to numeric columns.

Please let me know if this helps.

Regards Pilsner