Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

Dynamic Field Name when Load Script

Hi Expert,

I have a data table in excel like this:

Q1.jpg

And my expectation output should be like this:

Q2.jpg

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.

Tags (2)
3 Replies
jyothish8807
Honored Contributor II

Re: Dynamic Field Name when Load Script

//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

Best Regards,
KC
Highlighted
Sergey_Shuklin
Valued Contributor

Re: Dynamic Field Name when Load Script

Hello, Denny!

Or you can try this solution:

dynamic_headers.png

Partner
Partner

Re: Dynamic Field Name when Load Script

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