Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Changing date fields in header row in Alteryx Flow

Sasidharg
7 - Meteor

Hi All,

 

I m newbie to Alteryx and appreciate your support and response. 

 

We have dates as column header in Alteryx workflow.  My requirement is to read the changing dates

in the file and transpose the data.

 

Order  of the fields  is also to be modified and for this reason, we are using select macro however on

running the flow, it gives warning that the date fields are missing.

 

Input file format and required out put in excel along with my current workflow has more details.

 

Thank you again, 

 

Best,

Sasidhar 

10 REPLIES 10
atcodedog05
22 - Nova
22 - Nova

Hi @Sasidharg 

 

You can use transpose tool like below

 

workflow:

atcodedog05_0-1634834986271.png

 

Hope this helps : )

 

Sasidharg
7 - Meteor

Appreciate your help and response on the question and query.

 

Here are my source file samples. Each file has 100 weeks of data and is dynamic in nature (as in the attachments). 

 

Work flow to be able to load the either 1st file or 2nd file with out any modifications. I m seeing an issue at the transpose (checked unknown columns as well)  as it says the data field is missing and will be ignored. 

 

Thank you all once again, 

 

Best Regards,

Sasidhar 

Hello @Sasidharg 

 

You are getting this warning because the dates changed. The tool was configured to check specific set of dates then those dates changed... that's why you are getting the warning. If you check the outcome of file2, you will see that the new dates got transposed, and every date is showing 5 records. Hope that helps

Sasidharg
7 - Meteor

Hi Christine,

 

Thank you very much for your response. I m not just getting a warning that the dates have changed, also some dates and quantities  are missing after 2nd file is transposed. I m suspecting this is because of the date changes. 

 

How to have the transpose and still it interprets changes in the dates. Thank you very much for your info,

 

Best Regards,

Sasidhar 

Sasidharg
7 - Meteor

Hi Christine/All,

 

After changing the source file, i have to go to transpose macro and click on it manually so that the workflow recognizes changed header dates and runs workflow successfully.

 

Opted for warning message earlier on missing columns in transpose,however the totals in source and target are not the same

.Therefore changed it an error and below is the error i see (before clicking on transpose) 

 

Is there a way to automate this piece as well in Alteryx  so that we do not have to visit the workflow every time,

 

Thank you very much once again,

 

Best Regards,

 

 

 

Sasidharg_0-1634942574990.png

 

ncrlelia
11 - Bolide

Hi @Sasidharg,

 

I would suggest the below.

 

1. Read the file with 'First Row Contains Data' checked.

ncrlelia_0-1634963152418.png

 

 2. You then transpose the data with F1 being the key column. F1 will always be your category to group by.

ncrlelia_1-1634963247693.png

 

3. Filter out the rows which contains headers. For the attached sample data, that would be where F1 = 'Part'.

ncrlelia_2-1634963359752.png

 

4. You then JOIN the out from T (i.e. Headers value) with F(i.e. Data) on the [Name]. Select the required fields and rename them accordingly. Followed by a sort.

ncrlelia_4-1634963462287.png

 

Hope this helps.

 

Cheers,

Lelia

 

Sasidharg
7 - Meteor

Hi Christine,

 

Thank you very much for your response. Most of our input files(forecast data)  have changing dates in the header.

 

At this time, work around is to go into the work flow and click on the transpose so that this macro understands there is a  change in the header (dates)  before running the work flow. We are seeing some warnings with the changing dates however this is also causing a discrepancy in target numbers compared to the source which is our problem.

 

We would like to automate the Alteryx process, however for now i have to go to workflow to click on the transpose tool (manually)so that the macro identifies there is a change in column headers before processing it. 

 

Not sure if there should be a different macro before transpose to handle the scenario or if there is a work around to automate our workflow.

 

Please let me know if you would like to have other information from me or would like me to reach out to a different team. 

 

Thank you very much for all your help and also letting me know, 

 

Best Regards,

Sasidhar 

patrick_digan
17 - Castor
17 - Castor

@Sasidharg Have you ensured that all tools in your workflow have the unknown box checked? In your Demand Forecast workflow you posted last Thursday, there are two select tools and the transpose that all have the unknown box in your transpose unchecked. Once you check all the unknown boxes, I think the workflow should work as you expect. Any new fields would flow through your workflow.

Sasidharg
7 - Meteor

Hi Patrick,

 

Checked unknown fields in select and Transpose and now the workflow is able to handle changed date headers however i have to manually go into my work flow and click on transpose to resolve the error before running workflow successfully .

 

in other words, can we refresh meta data (using a  command prompt etc) before we start a workflow??

 

Thank you for your input all ,

 

Best Regards,

Sasidhar 

 

Sasidharg_0-1635564528435.png

 

Labels