Working with Tables in the Script

[#1]

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!

[swuehl · Accepted solution]

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.

[MayilVahanan]

Hi

Try a CROSSTABLE concept,

Ex:

CROSSTABLE (Year, Sales,1)

LOAD * FROM YourTableName;

Hope it helps

[#4]

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?