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:
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.
Text(stringfield) as stringfield,
STORE TempPart into [\\...\Part.qvd(qvd)];
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?
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.
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...
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.