QlikView Script Syntax Questions

[#1]

Team,

This is probably easy stuff to answer, but I cannot find it in the book...

When loading a table with an ODBC connection, I can perform SQL-like date functions like these below, calculating the Year, Month and Day from a single date (INVOICE_DATE).

    YEAR(INVOICE_DATE) as [Ship Yr],

    MONTH(INVOICE_DATE) as [Ship Mo],

    DAY(INVOICE_DATE) as [Ship Dy],

However, when I load from an Excel file, the syntax is different.  How can I do the same as above, but with an Excel table and QV script?

Also, if I am loading a table and want to add a single field with the same value for all records, how do I do that using QV syntax and SQL syntax?  For example, I want to add a field called "Site" with a value of "1001."

Can anyone point me to a good place to find the QV syntax?  I've been looking through the reference manual, but I can't seem to find a place that lays a lot of it all out for me.  I'm probably missing something totally obvious.  Thanks for the help!

[rustyfishbones]

Hi Shelley,

It's the same for when you are loading from Excel.

If you want to create a new field with the same value.

Just add

1 as MyNewField,

[rustyfishbones]

So it would look something like this in a Standard LOAD Statement

[rustyfishbones]

And for strings it would be

[#5]

Shelley,

The load from Excel should be the same.  If you want to add a new value (column) in the results after the initial load, use a preceeding load at the top.  Qlikview reads the load script from bottom to top something like this:

MyQVTable:
Load *,     
'1001' as Site;
SQL SELECT * FROM MySQLTable;

[stevedark]

Hi Shelly,

The code that you are showing a snippet of there, is that being done in QlikView code or SQL code?

When you load from SQL you can issue any transact statement you wish after the SQL prefix, eg:

LOAD

     *

      ;

SQL SELECT

      GETDATE() as Now,

      DAY(Date) as Day,

      MONTH(Date) as Month,

      YEAR(Date) as Year,

      Date,

      Description,

      SUBSTRING(Description, 1, 3) as Prefix

FROM dbo.MyTable;

In this case all of the commands are issued against the SQL database and the syntax is as per that of the database.

The 'preceding load' allows you to do all of the data manipulation you could do to fields in SQL, only using QlikView syntax.  The above statement could therefore be written as:

LOAD

     now() as Now,

     Day(Date) as Day,

     Month(Date) as Month,

     Year(Date) as Month,

     Description,

     mid(Description, 1, 3) as Prefix

     ;

SQL SELECT

     Date,

     Description

FROM dbo.MyTable;

Now, because when loading from Excel you don't get the separate SQL syntax part of the statement you are limited to only issuing QlikView statements, but you can do these in exactly the same way.

Something you need to be careful of when loading from Excel (or even more so text files) is the format of date fields.  If they are coming through as strings rather than actual dates then issuing date functions will fail.  In these cases you may need to convert the string to a date before issuing another function on them, eg:

  Day(Date#(Date, 'DD-MMM-YYYY')) as Day,

This depends entirely on your source data.

Hopefully that has given some pointers as to what you need to do here.

Please post back with more information if it has not.

Regards,

Steve