Dynamically select columns based on metadata file

[Willox]

Hey all, 

Apologies if there is a solution out there for this one already, I wasn't able to dig anything out that sounded appropriate.

I have a peculiar situation here, in that I have a large raw data dump from one of our systems that contains test results data. It's flattened so there is a row per person and looks something like this:

Dataset 1

Person	Test type	Test type component 1 name	Test type component 2 mark	TTC 2 name	TTC 2 mark	Misc. data 1 name	Misc. data 1 value	Misc. data 2 name	Misc. data 2 value
1	Maths	Algebra	4	Trigonometry	5	Shoe size	10	Age	4
2	English	Grammar	5	Spelling	8	Native Language	French	NULL	NULL

There are plenty more columns along these lines...! I have received a file of metadata from the administrators of this data explaining exactly which fields are relevant for reporting purposes.

It looks like this:

Dataset 2

Test Type	Field Name	Test Component	Relevant for reporting?
Maths	Test type component 1 name	Algebra	Yes
Maths	TTC 2 name	Trigonometry	Yes
Maths	Misc. data 1 name	Shoe size	No
English	Test type component 1 name	Grammar	Yes
English	TTC 2 name	Spelling	Yes
English	Misc. data 1 name	Hair colour	Yes

Effectively I need to transpose dataset 1, using the metadata, to a file that looks like this:

Person	Test	Test Component	Mark
1	Maths	Algebra	4
1	Maths	Trigonometry	5
2	English	Grammar	5
2	English	Spelling	8
2	English	Native Language	French

This means I need to dynamically select which columns to transpose based on dataset 2, selecting those only where the Field Name = Column name, the value in the field = Test Component and the relevant for reporting field = Yes.

Is there a way to do this?

[danilang · Accepted solution]

Hi @Willox

Here's a workflow that does your mapping.  As opposed to your idea of dynamcially choosing which columns to transpose based on the mapping, the trick here is to transpose them all into 2 groups first.  The Names columns (TTC 2 Name, Misc. data 1 name, etc) and the corresponding Value columns.  Then join these to have a series of records having one name and one value each.  Then it's a simple join to your mapping table to pull out the records you're interesting in.

Note: I added a record into your mapping table to pull the "French" record.

Dan

[Willox]

Thanks Dan, this should do the trick!