Dynamic output options based on specific value

Question

Hi,

I am dealing with an issue and I don't know how to solve it.

So, I am going to explain my problem.

I would like to use a dynamic option file base on a value from my data. I give you an example to be more clear. You can also look up to the worflow's example in the attachment.

So, I have differents products name with their quantity, and according to the maximum of the quantity, independently of the product name, I will have a specific output file.

If the quantity maximum is according to Product A, then I should have an 'csv' file, if it's the Product B, it would be a 'xlsx' file and so one.

I am stuck about the last step to give this value (the file's type) in the output option.

It's something I have to face with it multiple type, that is do some manipulation to get a specific value and then use it as variable.

This example could be apply with multiple other case, like desactive/Activate container, Detour worflow, based on a variable determated by data manipulation.

Thank you for you're help.

Dynamic output options.yxmc

apathetichell · Answer

@Vanderleck16Take a look at the attached batch macro. I don't think it's 100% complete for what you want, but this would get you over the crux of your problem. You will need to run two macros or (more likely) create the decision framework for inside your workflow if something is going to be .csv or .xlsx PRIOR to calling the batch macro.

Issues with your batch macro include that file-format (ie what type of file Alteryx is going to write) is a specific value and as an action tool the value is set/updated when your macro executes not during the workflow. Your control parameter controlled the name of the file - but didn't change the fileformat. So for example it could create a file called "thisisacsv.csv" but the contents of that .csv would be formatted as an .xlsx file - or vice versa. Your current macro would create files with .xlsx or .csv extension but were formatted as Alteryx Databases.  This batch macro can write either/both .xlsx or .csv depending upon which files are sent into it and will make dynamic changes to the file format as required. Obviously you'll need to integrate it into your workflow but it solves your major problem. Also - excel documents will still need the 'sheet1' (or whatever sheet) in the filename.

A few more notes - I'm not sure about what goes into render but I believe that since the fileformat properties of render are not changeable via action you could change filetypes via simple action tool set to rename with render. If you'd like other fileformats - let me know and I can update the action tools.

Hope this helps!

batch macro by type.yxmc

apathetichell · Answer

@HomesickSurfer @messi007 fyi - you'll need to automate changing @fileformat-value in output data too (via an action tool) to swap from writing in excel to .csv. Excel is fileformat 25 and .csv is fileformat 0.

HomesickSurfer · Answer

Hi @Vanderleck16

Modify your formula to specify the fullpath and sheetname for each output filetype based on max product quantity as follows:

IF
[First_Product]="A" THEN "C:	emp\Product.csv||Sheet1"
ELSEIF
[First_Product]="B" THEN "C:	emp\Product.xlsx||Sheet1"
ELSE
"C:	emp\Product.xls||Sheet1"
ENDIF

Also, configure your output tool to change entire file path based on the formula result.