topic Spaces in article number in QlikView

Spaces in article number

Fri, 26 Nov 2010 17:17:04 GMT

Hi,

I have a problem with loading the file article number from our company database.

There are article numbers that are different from each other only by the number of spaces. In qlikview only one article number is loaded.

Here my example:

1. Database query (Qlikview to Informix-DB): These are two different articles in the where clause!
SQL SELECT artikel
FROM cdix.fyar1sta
where artikel = '210 300 02' or artikel ='210 3 0002'

2. Listbox in Qlikview shows me only one entry.

Thanks in advance for your help!
Markus

Spaces in article number

Miguel_Angel_Baeyens — Fri, 26 Nov 2010 20:40:52 GMT

Hello Markus,

Take a look at this post regarding the use of TEXT() and representation in QlikView.

Table:LOAD Text(artikel) AS artikel;SQL SELECT artikelFROM cdix.fyar1stawhere artikel = '210 300 02' or artikel ='210 3 0002';

Besides, check that those balncks are plain spaces and not any ascii characters, and if they are, change them with the Replace() function:

Replace(FieldName, chr(X), ' ') AS FieldName

Hope this helps

EDIT: I add a sample code for your SQL statement so you can easily check whether the field is properly loaded

Spaces in article number

Sat, 27 Nov 2010 19:48:43 GMT

Hi Markus,

There are two reasons for this;
1) QlikView handles everything that can be interpreted as a number as a number.
2) A number is always displayed in the format it is first encountered.

A simple solution to this problem is to convert it to "text" using the "text()" function as suggested. In some cases this is however not sufficient as the conversion to number takes place already in the transfer from the "sql select" to the load statement. To handle this you must ensure that the value is text already in the SQL statement. The following is a solution that I have used succefully used several times:

Load
text(mid(Xartikel,2)) as artikel;
SQL SELECT
'X' || artikel as Xartikel
FROM cdix.fyar1sta
where artikel = '210 300 02' or artikel ='210 3 0002'

Good luck

BR
/Peter

Spaces in article number

MichaelRobertshaw — Sat, 27 Nov 2010 20:20:25 GMT

Have you tried the "Verbatim" special variable?

Spaces in article number

Mon, 29 Nov 2010 08:32:27 GMT

Hi community,

thanks a lot for your fast and perfect answers.
The solution for me was the text() funktion.

With
LOAD text(artikel);
SQL SELECT artikel
FROM cdix.fyar1sta
where artikel = '210 300 02' or artikel ='210 3 0002'

evrything works fine.

Regards
Markus