Is it possible to use a parameter to define which column to map/use?

[NicolaD]

Hello,

We use DI Studio 4.2.

I have a parameterised job within an iterative job. In my interative job I have a control table which feeds the loops with parameters for source_data. The parameterised job starts with a data set which contains a column (data_source) defining the source of the data this is in the rest of the columns (each column is a different attribute of the data such as age, client_type, ethnicity etc and then a value column).

The iterative job loops though the different data sources performs the same task in the parameterised job for each of them in order to prepare the data for charting in WRS, however for some of the data sources I want to use different attributes of the data, therefore the data is in a different column.

My job contains an extract with a where clause where Data_Source = "&Source_Data" this is then followed by a summary stats transform which sums the Value column grouping by a given attribute (i.e. a particular column is specified in the Class statement), as this column I want to use in the Class statement changes with the data source I need to know if I can use a parameter to tell the summary stats which column it needs to use.

I already have the attributes which are associated with each data source in my control table so these can be fed through the control loop.

[Patrick]

Hi
One way could be to map in the inner job all possible source columns to the target column and then use a 'case' in the expression where you query the current parameter value (macro variable) to pick the source column you want.
HTH
Patrick

[NicolaD]

Thanks for your suggestion Patrick, however I found a very powerful solution today by trial and error.....

I have a parameter called Category1 in the job (the parameter values are fed to the job via a loop).
In the extract transform, I didn't map any of the source columns across (except for the ones which are the same regardless of where the source data is coming from such as 'value' and 'AreaCode' etc), I then added a new target column, which I named Category1 (but this doesn't have to have the same name as the parameter) and in the expression I put &Category1. (the dot at the end was important as without it the resulting column was just blank, when I added the dot, the resulting column is the column that was defined by the parameter value). If you put double quotes around the &Category1 in the expression it resolves the parameter variable but then treats it as a string of text filling the column with that string rather than mapping a column across from the source data.

[Patrick]

Nice one! That generates code like: (&parameter.) as TargetColumn

So this will work with all transformations generating SQL code (one would have to test with the other ones).

Not sure why it wouldn't work without a dot (it did work in my test) - but having a dot at the end of a macro variable never hurts.

What you're loosing with not mapping all possible source columns is correct impact analysis on column level.
If you want to keep this then you still would have to map all possible source columns to target - and in order to not get a DIS warning also use them in your expression. The following expression did work for me:
&parameter /* sourceCol_1 sourceCol_2 sourceCol_3 */

DI then generated the following (Extract transformation):
(&parameter /* sourceCol_1 sourceCol_2 sourceCol_3 */) as targetCol

Message was edited by: Patrick