Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Working with Tables in the Script

Hello all,

I'm a new user to QlikView. 

I have a table in a source Excel file (which I can't change) with fields as follows: Company, 1990, 1991, 1992, 1993, etc. where "Company" is the name of the applicable company and the year fields contain sales information.  There are about 30 years in total.

For workability, I would like to change the structure of the table during the load to have only 3 fields: Company, Year, Sales and "stack" the year fields.

Has anyone else tried this? Could you recommend an efficient solution?

Thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: Working with Tables in the Script

Try a CROSSTABLE LOAD prefix, something like

CROSSTABLE (Year, Sales,1) LOAD * FROM YourTable;

Check the Help for more details on CROSSTABLE.

Regards,

Stefan

edit:

To make this work, your input fields need to be in order: Company, 1990, 1991, 1992, ...

i.e. Company should be first in order when loading from your source. You may need to specify your field order explicitely, if needed.

3 Replies
MVP
MVP

Re: Working with Tables in the Script

Try a CROSSTABLE LOAD prefix, something like

CROSSTABLE (Year, Sales,1) LOAD * FROM YourTable;

Check the Help for more details on CROSSTABLE.

Regards,

Stefan

edit:

To make this work, your input fields need to be in order: Company, 1990, 1991, 1992, ...

i.e. Company should be first in order when loading from your source. You may need to specify your field order explicitely, if needed.

Re: Working with Tables in the Script

Hi

Try a CROSSTABLE concept,

Ex:

CROSSTABLE (Year, Sales,1)

LOAD * FROM YourTableName;

Hope it helps


Not applicable

Re: Working with Tables in the Script

Thank you! This worked.

A follow up question: I simplified my example a bit - I had actually added a new field to my table in the LOAD statement.

LOAD

     .....,

     'Auto' as BusinessLine

FROM ..... 

Now that I have the

CROSSTABLE (.......) Load, the new field doesn't get added properly.

I've addressed this by loading a new table including all fields of the crosstable, adding this new field and dropping the old table. This works, but it makes my code quite long.  Is this the only alternative?

Community Browser