I am pulling together a report from a number of different sources. My key field joinng all my tables together is a number. In some of the data files it might be represented as 001234 and in another it will be 1234.
I am sure that this is a common problem and rather than try and re-invent the wheel I thought I would ask is what is the best way to solve this problem.
Yeah, I checked for that. Unfortunately for me, I am a consumer of the data. To further complicate matters, both the parent and child have two fields (both varchar) that indicate a records uniqueness, so I am already creating a composite key manually for QV. Part of the key is plan codes for medical plans. The application allows them to manually enter the plan codes which will be used as the primary key to be perpetuated. The database has a constraint on the plan code. The field is varchar however, so when one user enter "HYN" and another " HYN", the constraint allowed it.
The life lesson I have had to explain this to my business users several times: QV does not cover/eliminate underlying data or system issues; it probably exposes them faster.
Thanks Bruce
As an aside, the contributors on these forums have been an invaluable resource for the developers at my company. We have only been using QV for about 3 months and are trying to get up to speed.
In this case how we diffentiate these numbers as individual..Is there any way..I want to keep 001234,01234 and 1234 number as different numbers.By default QV will assume all these number as one i.e., 1234
sorry to dig out an old thread, i wanted to avoid opening yet another thread concerning leading zeros in the dataset.
The suggestions
load text(column) as column;
sql select column from table;
seem to be the canonical answer to the question so far in the threads i found. For this example (extracting a column 1:1 into qlikview), it works perfectly.
However, i have a slight problem in my script, as a lot of functions are called in sql.
For example, one line in my script right now is:
sql select nvl(possible_entry1,possible_entry2) as used_entry from table;
(nvl is an oracle function that gives possible_entry1 if it is not NULL and possible_entry2 otherwise).
Now i have the same problem again: possible_entry1 and possible_entry2 can have "1" and "001" meaning different things.
however:
load text(used_entry) as used_entry;
sql select nvl(possible_entry1,possible_entry2) as used_entry from table;
gives an error when executing the "sql"-Line:
Field not found - <used_entry>
How can I apply the Text-Function in this example?
"load text (nvl(...))" does not work because the Qlik-View does not know about nvl, this part is done on the oracle-side.
Remove the LOAD part and leave only the SQL part in your script. Note that fields are case sensitive in QlikView, and some drivers are not, so they will return likewise irrespective you use select FIELD or select field.
Leaving out the load part gets me back to square one: The Leading Zeros are mixed together, meaning that two values "0003" and "3" in the data end up either as "3" in qvw.
If you want to preserve the leading zeros, you definitely need the "load Text()"-Part.
My problem was that i thought this solution would not work if the column is not loaded directly (e.g. "sql select columname from table"), but as part of a function (e.g.: "select nvl(column1, column2) as somecolumn").
The mistake was that (with the oracle-driver) the columns are returned all in Upper Case.
So the suggestions from the other threads still hold true, if you want to preserve leading zeros, the easiest working solution i found so far is:
LOAD TEXT(SOMECOLUMN) AS SOMECOLUMN, COLUMN3, COLUMN4;
SQL SELECT NVL(COLUMN1, COLUMN2), AS SOMECOLUMN,COLUMN3,COLUMN4 FROM TABLE;
The solution works for me for all columns i had trouble with when creating the thread.
Thanks again to everyone involved, especially for jagan for pushing me in the right direction and qlikuser14 for spotting the real error in my script (forgetting that qlikview is case sensitive while oracle is not).
That is right, you either need the Text() or Num() function to keep leading zeroes.
Num(FieldName, '00000') // i.e.: in case your code is fixed to 5 digits
But removing the LOAD will help you check the names as they are retrieved by the driver and put into QlikView memory, which sometimes helps to understand where is the renaming problem (SQL or LOAD).
.png "Miguel_Angel_Baeyens"){kind=avatar}
