Create Dynamic columns

[priyarane]

Hi Community,

I have tried couple of ways but not getting exact one, can some one please help me to find the good way.

I have Soruce data like below and I have some rules in excel depending on that I have create columns and also source fields as well.
I wanted to drive columns automatically not manually depending on Excel/mapping file.
I have attaced sample data.

SourceData:
Dim1 Dim2 Dim3 Val
A A1 A2 10
AA AA1 AA2 112
AB AB1 AB2 22
B B1 B2 88

Mapping file/defined rules:
Source_Col Report_Col Mapping
Dim1 Source_1
Contra_Ref Contract Ref Dim2&Dim3

OutPut:
Dim1 Source_1 Contract Ref Sum(val)
A A A1A2 10
AA AA AA1AA2 112
AB AB AB1AB2 22
B B B1B2 88

-Priya

[effinty2112]

Hi Priya,

Maybe:

Data:

LOAD

Dim1,

Dim1 as Source_1,

      Dim2,

      Dim3,

      Val

FROM

Data_test.xlsx

(ooxml, embedded labels, table is Source_Table);

Left Join(Data)

load

Dim1,

Dim2 & Dim3 as Contract_Ref

Resident Data;

Giving:

Dim1	Source_1	Contract_Ref	sum(Val)
A	A	A1A2	10
AA	AA	AA1AA2	112
AB	AB	AB1AB2	22
B	B	B1B2	88

Regards

Andrew

[priyarane]

Thanks Andrew for the reply, but it is not exactly what I want.

I will simply my question again.

I have source for example: Dim1, Dim2

And I have Excel with mapping column line for Dim3 I have to create with Dim1&Dim2 and I don't wanted to write manually but it should be automatically take column Dim3 based on mapping rule in excel.