Data Table Transformation and Storing

[KLEITH21]

Hello, I am in need of a little help on how to transpose my input form data and write it out to individual file "databases" on a monthly basis. I imagine I need to transform the data structure so that the Current Month kW is the value of the System but I can't quite nail it down!

Incoming data schema

Required Output needs to write to separate files based on Region and then, within those files, tabs based on Site. The Current Month kW would append to the historical readings. It should look something like this unless someone has a suggestion on a better schema.

I tried this but this gives me too many columns for each so maybe I need to summarize and join rather?

[allwynthomas24]

@KLEITH21 

Does your incoming data schema have Date/Month Column ? Is it possible for you to send the data file for reference & better understanding.

Regards.

[KLEITH21]

@allwynthomas24  The incoming data schema is not set up yet but will be a SharePoint List, with the Current Month's kW field as the only variable field. I don't know SharePoint very well but I would need this List Form to empty itself in the Current Month's kW field after each monthly submission window is closed - would Alteryx be able to automate that process as well?

SharePoint has date created and modified fields.

[allwynthomas24]

Hey @KLEITH21 

Even I ended up making a similar workflow where separate files are being generated for each Region with separate sheets for each Site but all columns are present. Simple way would be to create another workflow with a Select tool to get rid of the extra columns.

  

Spoiler

[KLEITH21]

@allwynthomas24 This does help me with how to write & append to the correct files! Thank you! However, I think my issue is that each site has a unique amount of fields and "System" nomenclature. I need to be able to eliminate the "Systems" that are not applicable to that "Site" in these files (see in red).

Site ABC should not have Site GHI's Systems in the file and vice versa. I have 57 Sites and 150 Systems so the above Crosstab will not work. I know Union will not work either because the # of fields have to be the same and that is just not how my data is structured.

[allwynthomas24 · Accepted solution]

Hey @KLEITH21,

Please check the attached workflows. I have created 2 workflows: 
First workflow to Transform the data as per your requirement.

Second workflow to Remove Null Columns.

Thanks & Regards,

Allwyn Thomas

Spoiler

Spoiler

[allwynthomas24]

@KLEITH21 

Kindly acknowledge the solution and revert. 

Thanks & Regards. 

[KLEITH21]

@allwynthomas24 if sites and systems are added, would this affect the Multi-Field Tool in there? How do I allow the field selection in the Multi-Field dynamic?

[Luke_C]

@KLEITH21 - check that the 'Dynamic or Unknown Fields' is selected in the multi-field configuration. If it is, you should be good to go for future additions.

[allwynthomas24]

Hey @KLEITH21,

Thanks for your response.

As @Luke_C mentioned, please tick mark the checkbox for Dynamic or Unknown Fields in the Multi-Field Formula Tool in both the workflows if I haven't. 

Thanks & Regards,

Allwyn.