Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal will move to Qlik Community Oct 4. Temporary case outage Oct 3. READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
dennysetiawan
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.

3 Replies
jyothish8807
Master II
Master II

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

Hello, Denny!

Or you can try this solution:

dynamic_headers.png

dennysetiawan
Partner
Partner
Author

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