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

[sridharthemass]

 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

[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?

or

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 

[DawnDuong]

I will try to post a mock up after j have access to laptop again 

[sridharthemass]

Sure and haven't used Iterative macro - please assist in the workflow piece when you are free and thanks a lot for your help

[DawnDuong]

Hey @sridharthemass 

I used a batch macro instead, since it is easier to update the tab names.

Cheers.

[sridharthemass]

Getting this below error message in response when i am trying to open it

Failure to Import C:\Users\XX\Downloads\TestingSimpleBatchMacro.yxzp.
There was an error opening "C:\Users\XX\Downloads\TestingSimpleBatchMacro\TestingSimpleBatchMacro.yxmd":

Is there any other possible means to have at this workflow please