Data Type

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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.

[robert_mika]

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?

[#5]

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.

[Clever_Anjos]

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 

[#7]

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

[#8]

+

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))