Dynamic output options based on specific value

[Vanderleck16]

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.

[shreyanshrathod]

Hi @Vanderleck16 ,

If I understand correctly, you could use a combination of filters before writing your output.

E.g. In first filter, check if "output options" = 'xls' and at the 'T' anchor, connect an output tool that is configured to write output in '.xls' file.

Similarly, from the 'F' anchor, connect another FILTER and check if ''output options" = 'csv'. If it's true, then at 'T' anchor, connect another output file that is configured to write as 'CSV' output. Follow similarly for '.xlsx' type.

I am not sure if this was your requirement, but this is what I gathered from your explanation.

Hope this helps.

Regards,

Shreyansh Rathod

[Vanderleck16]

@shreyanshrathod Thank you for your solution.

Yes that could be a way to do it, but I am looking for a dynamic solution with only one output, and avoid error messages because there were no records in a data output.

As I've said, this kind of problem is reccuring, to use a value from the data as a variable parameter.

Thank for sharring you"re solution, I appreciate it.

[messi007]

@Vanderleck16,

In this case you don't need a batch macro. But you have just to put the path for each type and then use the option change entire path.

See below

Below the formula for each condition

attached the workflow

hope this helps!

Regards

[HomesickSurfer]

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:\temp\Product.csv||Sheet1"
ELSEIF
[First_Product]="B" THEN "C:\temp\Product.xlsx||Sheet1"
ELSE
"C:\temp\Product.xls||Sheet1"
ENDIF

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

[apathetichell]

@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.

[apathetichell]

@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!