Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Currently I am working with the data of varchar datatype so the field has a combination of numeric and alphanumeric values.
In a table, I have a data like 427, 00000427 so when I load the same into Qlikview it shows as one value with frequency 2 as '00000427'. It automatically prefix zeroes to 427, so when user checks from UI they raised a question regarding the same.
To avoid this scenario, I am using text() while loading the data in extract script so Qlikview considered 427, 00000427 as two different values. But I would like to know is there any other way to avoid this scenario as it occurs in my most of the tables and my backend is Bigdata, so to use text() I need to do preceding load in extract script which I don't feel as a good approach.
Please share if there is any other equivalent method for text() in Bigdata.
Please share your ideas.
Thank you,
Leni
May be this
Text(If(FieldName < 1000000, '0' & FieldName, FieldName)) AS FieldName
Or
LOAD *,
Text(FieldName) As FieldName1
FROM <Source>;
DROP Field FieldName;
RENAME Field FieldName1 to Simple;
Hi Anil,
Thank you for the reply.
Currently I am using the same to avoid the scenario but I just want to know is there any other better way to implement the same.
Currently I am using like below,
Load Field1,
Text(Field2) as Field2,
Field3;
SQL SELECT Field1,
Field2,
Field3
From Tbl;
Regards,
Leni
Qlik does not use data types, basically all values have a numeric equivalent and a literal equivalent. If you need to specify that 000427 is different to 427, then you need to let QlikView know by using the Text() version as you are already doing. Otherwise, QlikView will interpret 000427 as a numeric value and store as such.
Unless your driver allows to do that on the SQL part, what you are doing is the correct way of doing it.
Thank you Miguel.
But the problem here is, I am using 20+ tables with more than 15 fields and I am not sure about the fields in which the same issue is there. Currently we received this comment from business and in analysis we found this issue.
So I would like to know is there any other method to resolve this issue without analyzing each table and fields.
Thanks,
Leni
Well, unless they can change something in the data source, (like make the first 0 a capital o) it may look like fine in the query but definitely not in QlikView.
You can of course try and resolve that on the expressions instead, but with that size is very likely that performance degrades a lot for users.
Analyzing your tables and field contents before starting your BI project usually isn't such a big deal, and though it may look like a chore at first, Data Quality Analysis should be part of the early phases of every BI project. The rewards will be many.
On average, fields can be divided in types (or categories if you want):
I'm probably forgetting a few cases and a lot of nuances, by the idea (also based on my own experience) is this: if you are willing to do some data treatment upfront, you will avoid a lot of back/forth communication and maybe a lot of mysterious QVW behavior at the same time.
Best,
Peter