Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Qlikviews handling of Strings as numbers

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

Not applicable

Re: Qlikviews handling of Strings as numbers

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
Valued Contributor

Re: Qlikviews handling of Strings as numbers

Same problem here. Did you find any solution?

Not applicable

Re: Qlikviews handling of Strings as numbers

Whats your exact issue?

rsvebeck
Valued Contributor

Re: Qlikviews handling of Strings as numbers

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

Not applicable

Re: Qlikviews handling of Strings as numbers

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

Re: Qlikviews handling of Strings as numbers

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

Re: Qlikviews handling of Strings as numbers

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

Not applicable

Re: Qlikviews handling of Strings as numbers

Right!!!!

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

Thanks!!!!

Community Browser