Hi Team, Need quick help in below data: I have more than 20 columns in one data file and need to produce 21st column by adding values of those 20 columns. But there is no certainity that all 20 columns will be present in each month data.At times we can have only 15 columns or 17 columns or all 20 columns but need to produce the additional column with all columns which are present in data file. For example: FilenameCode ACode BCode CResultA121013B132015C123015 Result column is summation of Code A,B and C. But in next month we might not have Code C in input file and result should be summation of Code A and B. Similarly we might not have code A in future so result will be summation of Code B and C. . Absent column means no data in input file and hence result will be summation of only present column. How to resolve this as I have more than 20 columns ? Can you please help? Thanks

To sum data even if some columns are missing

Hi Team,

Need quick help in below data:

I have more than 20 columns in one data file and need to produce 21st column by adding values of those 20 columns.

But there is no certainity that all 20 columns will be present in each month data.At times we can have only 15 columns or 17 columns or all 20 columns but need to produce the additional column with all columns which are present in data file.

For example:

Filename	Code A	Code B	Code C	Result
A	12	1	0	13
B	13	2	0	15
C	12	3	0	15

Result column is summation of Code A,B and C.

But in next month we might not have Code C in input file and result should be summation of Code A and B.

Similarly we might not have code A in future so result will be summation of Code B and C.

Absent column means no data in input file and hence result will be summation of only present column.

How to resolve this as I have more than 20 columns ?

Can you please help?

Thanks

Datasets

Accepted answers

All comments

Felipe_Ribeir0

Hi @Polite123

One way of doing this:

To sum data even if some columns are missing.yxmd

DataNath

Hey @Polite123, after initially Transposing your data, within the aggregation configuration of the Cross-Tab tool there's an option to add a Total Column (can also add a Total Row if that's needed) and so you can do this in a couple of easy steps. As you're transposing first, this will also be fully dynamic to new fields coming in/existing fields dropping out. The Dynamic Rename at the end is a simple step to clean up the field names:

Add column total as part of the Cross-Tab:

Final product:

Thought I'd mention that, whilst it's slightly less dynamic, there is also an 'Add Totals' macro made by @MarqueeCrew and co which will do this for you with a simple tick box - you just need to tick new columns if they come in:

Have attached my zipped workflow with the macro within.

Create column total.yxzp

Polite123

@DataNath & @Felipe_Ribeir0 ,

Thank for quick reply.

Actually I am using a formula tool which does the summation operation.

Formula tool has Code A+ Code B +Code C.

So if Code B is not present it is giving an error.

Felipe_Ribeir0

Hi @Polite123

The formula tool is not the best way to this case, since it is not dynamic (like you are seeing). The approachs using the transpose/cross tab are good ways to approach this problem.

Polite123

@Felipe_Ribeir0 ,

Agreed.

But the issue is I dont have only one calculated field result.

I have Result A,Result B,Result C and so on based on different conditions by summing different columns.

Do you have any workaround for above issue?

Thanks

Felipe_Ribeir0

Your real input has different columns than the one that you shared at this topic? Could you share it (with dummy data)? For the one that you shared it i understand that it is doing exactly what you required:

"For example:

Filename	Code A	Code B	Code C	Result
A	12	1	0	13
B	13	2	0	15
C	12	3	0	15

Result column is summation of Code A,B and C.

But in next month we might not have Code C in input file and result should be summation of Code A and B.

Similarly we might not have code A in future so result will be summation of Code B and C."

To sum data even if some columns are missing.yxmd

DataNath

@Polite123 if you have different combinations of totals that you want to add to your dataset then you can keep the suggested approach with a couple of extra steps. Let's take a couple examples where I want the total of A & C in one field, and the total of A & B in another.

First, I'll filter the transposed data to only leave rows relating to those fields (the other filter is the same but for A/B instead of A/C):

After this, we then Cross-Tab the data as outlined in the response above:

A Dynamic Select tool is used to get rid of everything other than the Total column, as well as the Filename (used for the Join at the end):

The stream used for Code A&B is the same, so we get the same structure of outcome there too:

Now, we just use the Join Multiple tool to bring the original dataset together, along with the varying totals - within the tool, we just remove the duplicated [Filename] field and rename the totals so it's clear which columns they're made up from:

As @Felipe_Ribeir0 mentioned, this approach of first Transposing the data is fully dynamic to changes and so you can see that if we remove the Code A field in the initial data (which is used in both streams), this doesn't cause any errors in the workflow but instead it just doesn't get pulled into any calculations which we would expect:

Hope this helps - in your case, you'd just need a Filter > Cross-Tab > Dynamic Select stream for each calculation of various field combinations you want.