topic Same script, two files, one dropping preceeding 0's in QlikView

Same script, two files, one dropping preceeding 0's

adamdavi3s — Wed, 27 Oct 2010 09:40:52 GMT

Hi,

I have a basic SQL script to load cost centres into two separate dashboards:


load costc_code,
 costc_code_description,
 costc_description;

SQL select

d1.dim_value AS 'costc_code',
d1.dim_value + '-'+d1.description AS costc_code_description,
d1.description AS 'costc_description'
FROM agldescription d1
WHERE d1.client='SD' and d1.attribute_id='C1';

In one dashboard it loads the codes correctly e.g.

00080

00090

00091

However in the second it is loading as:

In BOTH cases it is loading the CORRECT value into the concatenated description. e.g. 00080-COSTCENTRE1

The code in the script is EXACTLY the same in both dashboards and both are using the same ODBC connection so I can't understand why I am getting different results...

Same script, two files, one dropping preceeding 0's

marcel_olmo — Thu, 28 Oct 2010 00:45:28 GMT

Could you upload a QV document with your example? It would be easier to understand why i'ts not working.

Regards.

Same script, two files, one dropping preceeding 0's

Anonymous — Thu, 28 Oct 2010 00:54:19 GMT

Data is the same, but formatting may be different.

Same script, two files, one dropping preceeding 0's

adamdavi3s — Thu, 28 Oct 2010 08:22:49 GMT

Hi Both,

I just created a blank file with the load script and it loads the items correctly (WITH the preceding 0), which suggests there must be something in one dashboard causing it to load in the wrong format, but I don't know what this could be??

Same script, two files, one dropping preceeding 0's

adamdavi3s — Thu, 28 Oct 2010 08:38:10 GMT

Test file now uploaded.

I've checked all the 'SET' clauses at the start of both files and they do not differ at all.

Same script, two files, one dropping preceeding 0's

marcel_olmo — Thu, 28 Oct 2010 08:40:49 GMT

If you assure me that they are exactly the same codes, the result cannot be different.

If you upload a QV with your info maybe we can help you. Otherwise, we're not wizards.

Regards.

Same script, two files, one dropping preceeding 0's

prieper — Thu, 28 Oct 2010 08:43:36 GMT

Hi,

if you wish to maintain the codes as string (i.e. with leading "0"), use the function TEXT(costc_code). This will force QV to treat it always as text.

HTH
Peter

Same script, two files, one dropping preceeding 0's

adamdavi3s — Thu, 28 Oct 2010 08:43:54 GMT

Hi Marcel,

The QV files are massive I am afraid, I will try and compress one down. I just wasn't sure if there was a setting or something that I could be missing!

Investigating further:

Both scripts are exactly the same, although the file which is not working correctly loads some additional data but it is not linked.

The file that is not working appears to be loading the data as numeric, whereas the working file is loading it as text but I can't see anywhere where this is specified

Same script, two files, one dropping preceeding 0's

adamdavi3s — Thu, 28 Oct 2010 08:46:45 GMT

Hi Peter,

I did initially try the text clause in the script but it made no difference at all (very strangely!) I am afraid!

Same script, two files, one dropping preceeding 0's

adamdavi3s — Thu, 28 Oct 2010 09:32:44 GMT

Sorted, I have no idea why its loading differently but if I actually remember to alias my text(costc_code) it works