Assumes 427 & 00000427 as 00000427 in Qlikview

[leni_harishwara]

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

[Anil_Babu_Samineni]

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;

[leni_harishwara]

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

[Miguel_Angel_Baeyens]

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.

[leni_harishwara]

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

[Miguel_Angel_Baeyens]

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.

[Peter_Cammaert]

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

• Key fields: length, content, formatting and everything else is critical, and the display function is non-existent. Treat them as text from the start
• Dimensions (text and values): do some quality checks (427 = 00000427? sometimes you want to drop tthe leading zeroes, sometimes you don't - wait for some form of AI if you want the computer to decide for you) and fill the missing/wrong ones with a known quantity, text value or even a NULL value (if you don't want them to show up in list boxes)
• Amounts: display characteristics are unimportant, it's the value that matters. Drop everything that doesn't fit the bill (occasional text values, currency symbols, indicators for special values, etc.). Replace them with 0's or NULL values.
• Information fields: can contain anything or nothing. If the display format is important to your users, treating them with the Text() function by default is a good routine technique. YMMV.

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