3 Replies Latest reply: Jan 22, 2013 2:09 PM by daneen000 RSS

    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!

        • Re: Working with Tables in the Script
          Stefan Wühl

          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

              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?

            • Re: Working with Tables in the Script
              mayilvahanan ramasamy

              Hi

               

              Try a CROSSTABLE concept,

               

              Ex:

               

              CROSSTABLE (Year, Sales,1)

              LOAD * FROM YourTableName;

               

              Hope it helps