Data rule_idserial_nocitycountryregionentity145melbourneaustraliaAUSA1146sydneyaustraliaAUSA1178adelaideaustraliaAUSA1133new yorkUSAUSA22889hong kongChinaCHB1296melbourneaustraliaAUSB1212sydneyaustraliaAUSC1 Metadata rule_idfield name1serial_no1city1country2serial_no2region2city2entity2country In the Ouput will contain an single excel file with different sheet names based on ruleid and its associated columns selected dynamically for the above input there will be two sheets with names '1' and '2' Note the metadata and Data tabs columns will change dynamically Can anyone help me in solving this using batch macro or any other feasible efficient solution Sample_data.xlsx

How to dynamically select columns and output to different sheets in same excel file

Data

rule_id	serial_no	city	country	region	entity
1	45	melbourne	australia	AUS	A1
1	46	sydney	australia	AUS	A1
1	78	adelaide	australia	AUS	A1
1	33	new york	USA	US	A2
2	889	hong kong	China	CH	B1
2	96	melbourne	australia	AUS	B1
2	12	sydney	australia	AUS	C1

Metadata

rule_id	field name
1	serial_no
1	city
1	country
2	serial_no
2	region
2	city
2	entity
2	country

In the Ouput will contain an single excel file with different sheet names based on ruleid and its associated columns selected dynamically for the above input there will be two sheets with names '1' and '2'

Note the metadata and Data tabs columns will change dynamically

Can anyone help me in solving this using batch macro or any other feasible efficient solution

Sample_data.xlsx

Macros

Batch Macro

Dynamic Processing

Accepted answers

DawnDuong

I'm using Alteryx 2021.2.1, probably a version control issue. I attached here the macro and the standard workflows.

All saved as relative paths, so should work.

You need to update the inputs' connection to the macro / or insert the macro to the workflows... either way should work.

Cheers,
Dawn

TestingSimpleBatchMacro.yxmc

TestingSimpleBatchMacro.yxmd

All comments

DawnDuong

Hi @sridharthemass

Perhaps i am overthinking. By “dynamically select” do you mean:

a) the user manually click and choose which fields should show when different rules Are selected?

b) the input is a fixed excel file which control how another excel file is processed and output?

If the answer is a) it can be relatively straightforward. If b then you need to have a number of interfaces which can cater to the max number of fields that can be selected per rule.

dawn

sridharthemass

b then you need to have a number of interfaces which can cater to the max number of fields that can be selected per rule. yes i tried with batch macro but i am getting error and can you help me if have similar workflow solution built up for this please

DawnDuong

Hi @sridharthemass

i mistyped in my earlier reply. If (b) then relatively straightforward!

i am commuting now so unable to post a workflow. I will describe a schematic below.

1) transpose your “input data” by selecting all the measurements columns (in this case, that looks like all columns except the ruleID) > this will generate a new data set where you have a “Name” column that contains the measurement fieldnames

2) connect the L input of A simple join tool immediately to the output from the Transpose tool. The R input of the Join tool connects to the Mapping field. The join conditions are on 2 fields:

- RuleID: RuleID

- Field_name: Name

3) connect the J output of the Join tool to a Crossrab tool, select group by “ruleID”, setting is such that thr Name field is pivoted to new columns, the data fields aggregation method set to “first”.

4) Use a table tool then a render tool set to batch by “ruleID” so that different ruleId are written to different tabs of a single excel output file.

hope the above makes sense

dawn

sridharthemass

First of All- Really thanks for me helping me out with the solution - when i tried the above approach say for rule_id=1, only serial_no,city and country should be populated but the cross tab output yields the other two fields such region and entity as well.

Any work around to fix this - why i am saying is that eventhough we can filter out the blank columns in rule_id=1 table in some scenarios we might not have data for some column in the data tab as well which might remove the schema

DawnDuong

Hi @sridharthemass

i forgot about the blank values. In this case use an interactive macro to iteratively loop through the Mapping data row by row. Let me know if you have used this technique before.

if you use this method, you can directly write to excel output without the render / report table tool.

cheers

dawn

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335