Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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?
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.
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
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
+
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))