[10SR4] How to know the type of a field in script

[nstefaniuk]

Hello,

I need to know the type of a field in a table during the script.

I load 2 columns from Excel file: A and B

A contains Dates, for example 2009-06-07

B contains numeric values, for example 40000

A and B have the same numeric value, which is 40000, but Qlikview has stored somewhere, in the Dual type, that A is a Date, and B is a number.

If, in Excel, I change the format of B and set it to Date, B will be retrieved as a Date in Qlikview.

Once the script is finished, we can see the type of fields in Settings -> Document Properties -> Tables

but I would like to know these types during the script, to change the script dynamically if a field is a Date or not.

Thanks a lot

[flipside]

If you create yourself an Excel ODBC DSN using the Microsoft Excel driver, then you can view the datatypes in the source Excel that way ...

You can try to discover this through logic in a script like this, but it's a bit limited ...

ODBC CONNECT TO [Excel Files;DBQ={excel filepath}];
Datatypes:
SQL SELECT
'Numeric' as Fld0,
IsNumeric(Field1) as Fld1,
     IsNumeric(Field2) as Fld2,
     IsNumeric(Field3) as Fld3,
     IsNumeric(Field4) as Fld4
FROM "Sheet1$";

SQL SELECT
'Date' as Fld0,
IsDate(Field1) as Fld1,
     IsDate(Field2) as Fld2,
     IsDate(Field3) as Fld3,
     IsDate(Field4) as Fld4
FROM "Sheet1$";

Hope this helps

flipside

[nstefaniuk]

He he interesting, I will look at that.

Maybe can I find the tags in QVD if I do a store / load, someone knows if we can extract tags from QVD ? Maybe I could just parse the QVD as XML.

I will check the ODBC CONNECT TO, it seems to be a perfect way to discover the datatype from Excel.

I will test this WE and revert to you.

Thanks

[flipside]

You could also use a macro to check.  Here is a great example ...

http://www.robvanderwoude.com/vbstech_databases_excel.php

... which returns the cell value [arrData( j, i ) = Trim( objRS.Fields(j).Value )], but you can also return the datatype code  ...

http://www.w3schools.com/ado/ado_ref_property.asp

flipside

[nstefaniuk]

Yes but I want to run my application on QV server and as far as I know macro doesn't work in the script on server.

Thanks

[flipside]

Some macros do work on the server.  Can't guarantee this would, though.

flipside

[nstefaniuk]

So I have extracted the tags from a QVD I create in the script, then read as XML.

I have had only one issue, that takes me hours to understand, because when you use Rename, Qlikview avoid to tag the QVD (amazing !).

You will have tags in the QVD if you do:

Table1:

Load

Today() As current_day,

Year(Today()) As year,

Col1&' !' As Col1

INLINE

[Col1

A

B

C];

Table2:

NoConcatenate Load

* Resident Table1;

Store Table2 into Table1.qvd (QVD);

Drop table Table2;

But not if you add this at the end and you refresh TWO TIMES:

RenameFields:

Mapping Load

*

INLINE

[Old,New

current_day,CUR_DAY

year,YEAR

Col1,COL1];

RENAME Fields using RenameFields;

The second time (and the times after) there is no more tag in the QVD.

I am in V10 SR4 32 bits

[barryharmsen]

A late reply, but maybe there's another option worth exploring:

• Open the Excel workbook as an ODBC data source: ODBC CONNECT TO [Excel Files;DBQ=C:\YourExcel.xls];
• Issue the SQLCOLUMNS statement to retrieve metadata for every column in the workbook;
• Loop through the result set to dynamically generate your script, using the TYPE_NAME field to determine if a column is a DATETIME or not.

Using this logic you could read any Excel file into QlikView, and would not need to know anything about its structure, it can all be determined at runtime.

Also, when I'm not sure how a certain field needs to be interpreted or how it is formatted, I find the Alt function to be very useful.