Dynamic Field Name when Load Script

[dennysetiawan]

Hi Expert,

I have a data table in excel like this:

And my expectation output should be like this:

But the field name in Row 1 (in Picture 1 above) is dynamic, which means can be changed everytime.

I put a script like this:

LOAD

     Date(A,'M/DD/YYYY') as [DATE__1FCPOX8],

     Num(B,'0.00') as [VALUE__1FCPOX8],

     Date(C,'M/DD/YYYY') as [DATE__1FCPOZ8],

     Num(D,'0.00') as [VALUE__1FCPOZ8]

FROM [$(MySourcePath)];

But it didn't covered a dynamic name field.

Do you have any solutions about this case?

Thank you.

[jyothish8807]

//Hi Denny,

Try like this:

A:

LOAD @1

FROM

(ooxml, explicit labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0))),

Transpose()

));

Let vField1=peek('@1',0,'A');

Let vField2=peek('@1',1,'A');

Let vField3=peek('@1',2,'A');

Let vField4=peek('@1',3,'A');

B:

LOAD CalendarMonthNumber as [$(vField1)_ABC],

     CalendarMonthName as [$(vField2)_ABC],

     Actuals as [$(vField3)_ABC],

     [Required Run Rate] as [$(vField4)_ABC]

FROM

(ooxml, embedded labels, table is Sheet1);

In table A, simply replace the path with your path and change the format if yours is different.

Then create variable depending on the number of fields you have, you can automate this also by using loop, I just build this in hurry .

Br,

KC

[Sergey_Shuklin]

Hello, Denny!

Or you can try this solution:

[dennysetiawan]

Hi Jyothish,

Looks like the solution you describe is almost correct.

For this section:

Let vField1=peek('@1',0,'A');

Let vField2=peek('@1',1,'A');

Let vField3=peek('@1',2,'A');

Let vField4=peek('@1',3,'A');

Could you bring the sample script to automate this by using loop? In case I have a large number of field.

Thank you