topic Re: Dynamic Field Name when Load Script in QlikView

Dynamic Field Name when Load Script

dennysetiawan — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Dynamic Field Name when Load Script

jyothish8807 — Wed, 24 Oct 2018 05:39:20 GMT

//Hi Denny,

Try like this:

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');

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,

Re: Dynamic Field Name when Load Script

Sergey_Shuklin — Wed, 24 Oct 2018 05:46:38 GMT

Hello, Denny!

Or you can try this solution:

Re: Dynamic Field Name when Load Script

dennysetiawan — Wed, 24 Oct 2018 10:15:16 GMT

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