topic Re: Data Type in QlikView

Data Type

Fri, 15 May 2015 11:21:48 GMT

Hi All,

For finding out the data type of a column, we use istext(ColumnName). This would return -1 if ColumnName is in text format.

Now, if I want to store the column name in a variable and then use it. e.g

LET vColumn= FieldName(1,Test);

vColumn should store the first column name present in the table Test.

Now to check if this column had text data type, if I use

if(IsText($(vColumn))=-1,'Text','Other')as [Columns Data Type]

The above expression gives me an error. Error in Expression ')' expected.

If I use

if(IsText('$(vColumn)')=-1,'Text','Other')as [Columns Data Type]

(with '$(vColumn)'), there is no error, but it returns me the data type of the column name. I want the data type of a column.

Thanks,

Asma

Re: Data Type

Gysbert_Wassenaar — Fri, 15 May 2015 11:42:21 GMT

IsText doesn't work on fields but on field values and expressions. If you want to know if a field contains text or numbers (Qlikview doesn't have more data types) you can look at the tags of the fields on the Tables tab of the Document Properties window.

Re: Data Type

Fri, 15 May 2015 12:11:00 GMT

Is there any other way possible to check if the particular column is in text data type in script.

Instead of using the fieldname, I wish to use a variable which stores the fieldnames.

Re: Data Type

robert_mika — Fri, 15 May 2015 12:37:05 GMT

What is the purpose of this exercise?

Qlikview as such does not have data type.

There are different presentation of numeric values and where possible Qlikview returns two data values.

What if you have numbers and text in the same column?

What would be your expected outcome?

Re: Data Type

Fri, 15 May 2015 12:59:48 GMT

I just want to separate the columns which are only in text format.

If both numbers and text are present, then it should be represented as text.

Re: Data Type

Clever_Anjos — Fri, 15 May 2015 14:00:16 GMT

A not perfect solution would be:

Transactions: // table to be inspected
Load
 TransLineID, 
 TransID,
 mod(TransID,26)+1 as Num,
 Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
 Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
 Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
 Round(1000*Rand()*Rand()*Rand1) as Expression1,
 Round(  10*Rand()*Rand()*Rand1) as Expression2,
 Round(Rand()*Rand1,0.00001) as Expression3;
Load 
 Rand() as Rand1,
 IterNo() as TransLineID,
 RecNo() as TransID
Autogenerate 1000
 While Rand()<=0.5 or IterNo()=1;


for i = 1 to NoOfFields('Transactions')
  LET field = FieldName(i,'Transactions');
  LET flag  = IsText(FieldValue(FieldName(i,'Transactions'),1));

  Metadata:
  LOAD
  '$(field)' AS  FieldName,
  '$(flag)' as Flag
  AUTOGENERATE 1;
next

Re: Data Type

Wed, 17 Jun 2015 19:40:07 GMT

Faced same type of issue , read Qlikview File as XML. REload the qlikview Document twice.

FieldsAndTags:

LOAD String%Table,

'bnvafkrnl' as noconcat,

%Key_FieldDescription_8942ED1DAD568474

FROM [qlikviewfile.qvw] (XmlSimple, Table is [DocumentSummary/FieldDescription/Tags/String]);

JOIN (FieldsAndTags)

LOAD

Name as Feildname,

%Key_FieldDescription_8942ED1DAD568474

FROM [Qlikview.qvw] (XmlSimple, Table is [DocumentSummary/FieldDescription]);

Adhoc:

LOAD //Dimension

Distinct

// %Key_FieldDescription_8942ED1DAD568474,

String%Table,

Feildname AS Name,

'Dimension' AS DataType,

'Text' AS Subtype

Resident FieldsAndTags

Where String%Table='$text' OR Isnull(String%Table)

LOAD

//Timestamp to Date

Distinct

// %Key_FieldDescription_8942ED1DAD568474,

String%Table,

Feildname AS Name,

'Numeric' AS DataType,

'numeric' AS Subtype

Resident FieldsAndTags

Where (String%Table='$numeric')

and (not Exists(Name, Feildname))

Hope this helps.

thanks

Ranjith

Re: Data Type

Wed, 17 Jun 2015 19:43:58 GMT

Handling date and TimeStamp. In the below example I considered Date field as dimensions, if you want to call it different make sure to chantge below at specified places.

LOAD

//Date

Distinct

// %Key_FieldDescription_8942ED1DAD568474,

String%Table,

Feildname AS Name,

'Dimension' AS DataType, if you want consider dates as other dimension type please change here.

'Date' AS Subtype

Resident FieldsAndTags

Where String%Table='$date'

;

LOAD

//Timestamp to Date

Distinct

// %Key_FieldDescription_8942ED1DAD568474,

String%Table,

Feildname AS Name,

'Dimension' AS DataType,// if you want consider dates as other dimension type please change here.

'Date' AS Subtype

Resident FieldsAndTags

Where (String%Table='$timestamp')

and (not Exists(Name, Feildname))