Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qlikviews handling of Strings as numbers

Ok I am having some issues with this.

I discovered this quite some time ago that there were part numbers that Qlikview was reading as a number when it should always be a string (For us). This was causing issues with linking and duplicate numbers and lost data:

Example:

Part:

8329488

08329488

Qlikview read both of these values as 08329488.

I discovered that this could be fixed by applying the text function to the field to force Qlikview to read this as text. Since this happens after the data is loaded this needed to be done in the load script.

I use QVD's across several of my dashboards. Since this happens the first time data is pulled into Qlikview I have been creating a full load for these tables and have applied the text function to every field that is classified as a string. I then store this table into the QVD for other dashboards.

There are then cases where I need to use this table several times, so I then just resident load this original load where I have applied this function.

Example:

TempPart:

Load

Text(stringfield) as stringfield,

numberfield,

datefield;

SQL Select

stringfield,

numberfield,

datefield

From ......part;

STORE TempPart into [\\...\Part.qvd(qvd)];

part:

LOAD

stringfield,

numberfield,

datefield

Resident TempPart;

part2:

LOAD

stringfield as Newstring,

numberfield as Newnumber,

datefield as Newdate

Resident TempPart where stringfield<>'A';

Drop Table TempPart;

Ok so now finally to my issue. I was assuming that the original Load with the temp table would apply text to this field anywhere that this field is referenced(IE resident loaded). I have been looking at my data and this is not happening.

Do I really have to apply the Text() function EVERY place that I load this field?

As I said this is causing major issues with duplicate data and incorrect linking.

Can anyone shed some light on this for me?

EDIT:

I left out something because I thought it was unrelated. Our system requires preserving leading spaces. In order to keep these I have also used Set Verbatim='1';

I then also apply rtrim() to every text field to drop only the trailing spaces. (This was a TON of work to do to every table that I call in every dashboard hence the frustration)

I was able to at least get the Load section created for every table using a field output from SQL in Excel using formulas to check the field type. An example would be:

rtrim(text(field)) as field,

What it now seems to be is that Qlikview is ignoring the text function when it is inside of the rtrim function....Do I really need to rewrite this EVERYWHERE that it is used to text(rtrim(field)) ???

Sorry for any hostility as I said this is extremely frustrating as I spend around a week going through and rewriting these scripts to use these functions.

10 Replies
Not applicable
Author

You could use Num(stringfield,'0#########')

Not applicable
Author

I don't want to see this as a number. As I said I need all fields that are marked as nvarchar from the SQL database to be read strictly as strings in Qlikview.

RSvebeck
Specialist
Specialist

Same problem here. Did you find any solution?

Svebeck Consulting AB
Not applicable
Author

Whats your exact issue?

RSvebeck
Specialist
Specialist

Hi

In my DB2 database there are item numbers like yours, example 001233 and 1233 as two different items.

Then, when loding into Qlikview they are turned into the same 001233, I would at least have expected the opposite, that they both turned into 1233... but same bad...

However, I started reading this thread: http://community.qlik.com/message/245028#245028 and understood the matter better, and apparently the only current solution is to combine the SQL SELECT with a LOAD *,text(FIELD) syntax ... so I have to rewrite a lot of my scripts...

Best Regards Robert

Svebeck Consulting AB
Not applicable
Author

Yes, thats correct. I believe you can preload the table and apply the text(field) as field in the load statment, and then resident load without issue (or QVD) I am still working that part out however.

Yes it is a lot of work. We are upgrading our ERP software however so I was already doing some major script changes. I made it easier on myself by getting an output on all the database tables and field types and using excel to create my first load for me with formuals.

Not applicable
Author

Hi all!

I'm in the same situation...

I create QVD from text file, and qlikview  (release 11 sr 2) read '3' and '03' like '03'.

the values '3' and '03' are different entities.......

Help please!

Not applicable
Author

The formula Text(Field) as Field should solve this.

Not applicable
Author

Right!!!!

I use text(fieldname) when a read the text file to create qvd!!!

Thanks!!!!