[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

[Gysbert_Wassenaar]

If you want to make sure a field is of a certain type then in the load script explicitly make it that type. Generally it's best never to assume if you can make sure instead. Same here

[nstefaniuk]

But I don't know if this field is a Date or not, I need to guess it to do actions.

I explain:

I load an excel file. If there is a date field, I want to dynamically add 3 fields : Year, Month and Week for this date. If the field is named A, I want to add A_year, A_Month and A_Week.

Of course I could take the field A, control if all values are between 25 000 and 50 000 and if yes, create a copy of this field and name it A_Date, and 3 others fieds A_Year, A_Month, A_Week but it would be ugly.

Qlikview has a method to choose the datatype (given by Excel or database, and guessing through paterns for text files) and I would like to use it, instead of coding my own.

[Gysbert_Wassenaar]

I still don't understand. Are you saying that you have a column in excel that has both dates and numbers?

[nstefaniuk]

No I say that I have dynamic number of columns in Excel and I don't know if it's they are date or a number (but it's assumed that each column has only 1 type) and I want to dynamically add 3 more columns for each source column with type Date.

[Gysbert_Wassenaar]

So, you are saying you don't understand your source data? Somebody is handing you data and you have no idea what exactly is in which column?

[nstefaniuk]

Exactly, I don't know how many columns I will find in the Excel and their type.

It's just for quick and dirty analysis, it's not for a critical dashboard in production with thousands users.

[Gysbert_Wassenaar]

Aha. Ok, what I would do is first study the excel file and identify any columns that contain dates. If necessary ask the person that gave you the excel file. Once you know which columns are supposed to contain dates (no matter how formatted) you can force them to date type in qlikview. You can also try fixing the format in excel first, but explicit casting is better imho.

[nstefaniuk]

Yes but HOW identify which columns are dates or not ? I have already thought about algorithm to identify a column with dates or not (all values between 25000 and 50000) but I would like to avoid to do this, when Qlikview just read the datatype in Excel data model.

I am searching for a way to read the datatype that Qlikview has retrieved from Excel.

[swuehl · Accepted solution]

QlikView certainly has some intelligent methods to determine the "type" of a field (so I think QT won't call it type, but just a tag). A field can have multiple tags, and tags can be set by user.

Unfortunately, I don't know any function to call to retrieve the tags for any given field. The Help also says:

"The following tags are also automatically generated at the end of script generation, but may be altered or overridden using script syntax, see Tag Field and Untag Field. "

So I would deduct that tags are not fully available during script execution.

So, the only way I can think of to create something you want to achieve would involve multiple stages:

- Loading in your excel

- Exporting the meta information (tags) from your qvw.

- Reading in your original data together with exported field / tag info to do control the actions you want to perform

Seems all quite a lot of work for a quick & dirty analysis.