topic Re: difference between 1 and 001 in QlikView

difference between 1 and 001

Anonymous — Thu, 09 Jan 2014 09:46:55 GMT

Hello,

I have a problem with 0 bevore number in data mart (QV 11.2 SR3).

First I load from SQL a table with profit accounts. In this table there are two accounts 12345 and 0012345. Aftewards I load a table with values and join to the accounts table. The table with values has accounts 12345 and 0012345 too.

The problem: QlikView doesn't make difference between this accounts, shows in front end only 0012345 with count 2 and maps the values to both accounts cross over.

In script I define the format of the account field as Text(). Without success.

Any Ideas?

Re: difference between 1 and 001

luciancotea — Thu, 09 Jan 2014 09:53:06 GMT

This will force QlikView to treat it like a text:

CUSTOMERS:

SQL SELECT '_' & ID as ID,

CustomerName,

Address,

...

FROM Customers;

Re: difference between 1 and 001

jonathandienst — Thu, 09 Jan 2014 09:53:48 GMT

Qlikview interprets these as numbers. Use the text() function in the load to keep them in text format:

LOAD text(account) As account,

...

HTH

Jonathan

Re: difference between 1 and 001

swuehl — Thu, 09 Jan 2014 09:54:16 GMT

Text() should do the trick (used in the LOAD statement). How does your code look like?

Re: difference between 1 and 001

luciancotea — Thu, 09 Jan 2014 09:57:55 GMT

When using:

LOAD Text(ID) as NewID;

SELECT ID

FROM Table;

...the thing is that TEXT() is coming to help a little too late. QlikView already assumed that ID is numeric and made the transformation in SELECT section before passing it to LOAD section.

Re: difference between 1 and 001

Thu, 09 Jan 2014 10:05:05 GMT

Hi Dimitri,

Use the function Num() and convert them into number and chek.

Santhosh G

Re: difference between 1 and 001

Anonymous — Thu, 09 Jan 2014 10:10:17 GMT

How do you mean, Text is coming to late?

My script

KONTEN_SUM_TMP:

LOAD UPPER(Text(KONTO.GUV.SUM.KONTO)) as KONTO.SUM.ACCOUNT,
---
SELECT *
FROM dbo."V_FIN_D_KONTEN_R80";

Join(KONTEN_SUM_TMP)
LOAD UPPER(Text("GUV_SG")) as KONTO.SUM.ACCOUNT,
---
From ..\..\Finanzen\4_QVD\FAKTEN_GUV.qvd(qvd)
WHERE Year(GUV_DATUM) >= $(strYEAR);

Re: difference between 1 and 001

luciancotea — Thu, 09 Jan 2014 10:23:24 GMT

LOAD *;

SELECT * FROM table;

is a two-steps process: first the SELECT is executed and the data is imported in QlikView; second step is the LOAD part, when applying QlikView specific functions to data already loaded.

Automatic data transformation is performed in step 1.

More details: To guess the fields type, QlikView is looking into a small set of your data. In your case, in KONTO.GUV.SUM.KONTO field he sees only numbers (he is looking only in the first few records, he is not parsing everything) and he decides that it is numeric. Any future value in this field will be treated as numeric (if possible).

Now, another trick to tell QlikView the correct types of your fields is to perform an initial inline LOAD with dummy data, but each column having the right type. After that, QlikView will not try to guess the fields type because he already have them (remember autoconcatenation).

In the end, you can remove the dummy data.

Re: difference between 1 and 001

maxgro — Thu, 09 Jan 2014 10:35:00 GMT

Hi Lucian

strange, I had the same problem and solved it after the same suggestion (use text ()....)

http://community.qlik.com/thread/102160

Re: difference between 1 and 001

luciancotea — Thu, 09 Jan 2014 11:03:05 GMT

Like I said, QlikView performs a check on a small set of data in the first step. In your case he didn't encountered only numeric.

That is why text() doesn't always work.

Re: difference between 1 and 001

Anonymous — Thu, 09 Jan 2014 13:02:29 GMT

Unfortunately is it the right solution. I expected more intelligence from QlikView.

The script command Text() doesn't always work (see below). If I easy load account list and calculate, it works fine.

In my case I loaded the account list at the first step from SQL parent child table with hierarchybelongsto command.

Here doesn't work the commant Text(). Finally I used the tip above in the SQL statement. It is ok now.