Hi,
Need help or guidance with a workflow I am working on where the output data is to be structured based on dates (Cflow_dt field) in the input file. I have created a test workflow using the Cross Tab and Formula tools to get the desired output. While this works fine, the issue is dates in the "Cflow_dt" in the input file change each time a new input file is provided and the workflow fails as the dates coming out of the Cross Tab into the Formula tool are hard coded (fixed based on a input file). So, each time a new input file is provided, the date fields in the "Data Cleansing" and "Formula" tools need to be manually updated for the workflow to run ok. Is there a way to make the workflow independent of the changing dates and work without having to manually update dates for each input file.
Below is the test workflow. Also attached are the test input file and the workflow yxmd file.
Input File Format - is below. The Cflow_dt in the input file will always have at least 2 or more distinct dates and these would change for each input file. There will only be one input file to the workflow for each run.
Cross Tab tool configuration is below
Output from Cross Tab tool is
Formula tool configuration is
Final output data is filtered for records with GrandTotalUSD >= 10 million.
Any help or guidance would be much appreciated.
Thanks,
Raj
Solved! Go to Solution.
Hello @RajR ,
This would do it:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
Thanks a lot @afv268, that did the trick!
I just needed the output to be in the same original format (including the different Cflow_dt dates fields), so just added a Join tool to the join the output from the Transpose --> Summarize tool & Cross Tab tools as below. This is working as expected irrespective of the different dates and number of distinct dates under "Cflow_dt" field in the input file.