Changing dates in column name used in formula

[yxt]

Hi,

I have an existing formula that reads off a column name 'Holdings as of 08/15/18' .. The workflow works fine until we start loading new data set as input source files which will have latest holding date, for example 'Holdings as of 01/01/19'. The formula generates an error because the specific holding date in the original workflow relies on 08/15/18. 

Could we at the onset of the workflow number each columns as cells (like excel) and towards end of workflow convert them back to its original column name (based on the latest input source file)? By doing so, I acknowledge i may lose out on the intuitiveness of seeing the column name used in the formula.

Or is there a better solution to this? Could the formula recognize only partial of the column name 'Holdings as of' and continue off it? 

Thanks!

 

[DavidP]

In the attached example I'm creating a copy of the Holdings as of field and renaming it to denominator, that you can then use in your formula. Hope this helps.

[yxt]

Hi DavidP, i think we're almost there! I failed to mention earlier that the data may include several holdings as of dates for data comparisons later down the workflow.

How do we denote holdings as of 08/15/18 as denom1 , holdings as of 09/15/18 as denom2 ? Thanks!

[cmcclellan]

So how many is "several" ?

[DavidP]

Would something like this work?

[yxt]

very close! I'm having issue figuring out the cross tab function now. I followed your choice of group data by record id, new col header name and value for new cols. The output organized the header names by alphabetical order instead of it's original order. I guess i have to add joining tools 

[DavidP]

It's the Group by RecordID in the Tile tool that's messing up the order of your columns. If you tick the option just above it to "leave unsorted", that won't happen.

The CrossTab tool will reconstruct your columns in the row order that they are in at its input, so if you want to specify or change the column order, you need to do it before the CrossTab tool. 

Hope that makes sense.

[yxt]

Hi David. Despite using 'leave unsorted' in the tile tool, it is still sorting alphabetically. attaching my workflow. thank you

[DavidP · Accepted solution]

I had a look at your attached workflow. I can't do any testing without the input file, but I spotted a few things that makes me think that it's not actually working the way it should.

In my example I use the Multi-field, Transpose and Filter tool to achieve the following:

1. Create a copy of every field with prefix New_ added to the fieldname, but only copy the values for fields with names containing "Holdings as of" and leave all other "New_" fields empty.

2. Transpose all the fields except RecordID

3. Use a filter tool to remove all rows where Value is empty. 

The purpose of this is to be left with the original dataset and only copies of columns containing "Holdings as of". For this to work, the multi-field tool has to have this formula:

if contains([_CurrentFieldName_],'Holdings as of') then [_CurrentField_] else '' endif

and you need to have the filter tool with condition !Isempty([Value]).

You can now rename all your fields with names containing "New_Holdings" to something else, i.e. "denominator" (so that you are leaving your original "Holdings as of" fields alone) and use the tile tool to give it a number if there is more than one.

The Tile tool needs to be set up with Unique field as [Name], "Leave unsorted" ticked and Grouped on "RecordID".

The final formula tool then adds "TileSequenceNum" to "denominator", before flipping it all back with the CrossTab tool, grouped by RecordID.

I've made the changes to your workflow, but can't test it without the data file, so hopefully it works. Let me know if it doesn't.

[DavidP]

If you didn't care about retaining the original "Holdings as of" fields, you could remove the Multi-Field and Filter Tools altogether and simplify the whole lot like this, which is probably what you wanted to do. (new workflow attached)