topic Re: Working with Tables in the Script in QlikView

Working with Tables in the Script

Tue, 22 Jan 2013 14:40:52 GMT

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!

Re: Working with Tables in the Script

swuehl — Tue, 22 Jan 2013 14:55:06 GMT

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

MayilVahanan — Tue, 22 Jan 2013 14:57:22 GMT

Try a CROSSTABLE concept,

Ex:

CROSSTABLE (Year, Sales,1)

LOAD * FROM YourTableName;

Hope it helps

Re: Working with Tables in the Script

Tue, 22 Jan 2013 19:09:51 GMT

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?