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.
