topic Re: Excel load optimisation in QlikView

Excel load optimisation

Peony — Sat, 16 Nov 2024 02:00:03 GMT

Hi all.

I am loading data from an excel file. Usually it looks something like this:

Excel_Table:
Load

[2018-11] as [Data_2018-11],
[2018-12] as [Data_2018-12],
[2019-01] as [Data_2019-01],
[2019-02] as [Data_2019-02]

From Excel_file;

When the user managing this file removes columns from it or adds new ones, I need to manually edit the script.
Is there any way to optimize the process?
It is possible to pick up columns through an asterisk and use their names to automatically generate aliases?

Re: Excel load optimisation

tresB — Fri, 08 Nov 2019 07:24:37 GMT

Try like:

Excel_Table: Load * From <>; For f = 1 to NoOfFields('Excel_Table') Let vField=FieldName($(f),'Excel_Table'); Let vFieldNew='Data_' & '$(vField)'; RENAME Field $(vField) to $(vFieldNew); Next f;

Re: Excel load optimisation

Peony — Fri, 08 Nov 2019 08:17:33 GMT

Thank you! This is exactly what I was looking for.

The only thing I have added is single quotes around the variables on this line: RENAME Field '$(vField)' to '$(vFieldNew)';