Feldinhalt wird im QlikView nicht gleich ausgegeben wie im Oracle SQL Developer

[#1]

Hallo zusammen

Ich frage eine Oracle Datenbank ab. Der Software Hersteller hat Änderungen an der Datenbank vorgenommen, kann mir aber mit meinem QV Problem nicht weiterhelfen.

Im Oracle SQL Developer erhalte ich die korrekten Werte, die den Werten in der Applikation entsprechen. In QlikView hingegen, erhalte ich die meisten (also nicht alle...) Datensätze mit führenden Nullen. Das Feld ist in Oracle mit dem Format VARCHAR2(10 CHAR) festgelegt. Die Oracle DB ist auf 11.2.0.1.0.

Ich gehe davon aus, dass der Hersteller die Felder nicht sauber abfüllt. Woher kommt es aber, dass ich mit QV nicht dasselbe Resultat erhalte wie mit dem SQL-Developer? Kann ich das in QV beeinflussen? Bei den Daten handelt es sich um eine Colorit Angabe (das Colorit 00 hat also seine Berechtigung 🙂

Das Resultat mit dem Oracle SQL Developer sieht so aus:

SQL-Statement:

select ekstpcol

from ekstpos

Resultat:

00
20
30
50
00
20
21
21
25
00
21

Das Resultat mit QV sieht so aus:

Select Statement:

SQL SELECT

EKSTPCOL

FROM EKSTPOS

Resultat:

0000
0020
0030
0050
0000
0020
0021
0021
25
0000
0021

Danke

Christoph Geiser

[Gysbert_Wassenaar]

Vielleicht klapt es auf diese Weise:

Daten:

LOAD Text(EKSTPCOL) as EKSTPCOL;

SQL SELECT

EKSTPCOL

FROM EKSTPOS

[#3]

Thanks for the hint (ich sollte englisch schreiben )

Unfortunatly it doesn't work with that. Same format. Actualy it seems to be text format allready, only that in SQL Developer it looks different...

[#4]

Hello everybody

I ask from an Oracle database. The software developer has made changes to the database, but can not help me with my problem QV.

In Oracle SQL Developer I get the correct values ​​that match the values ​​in the application. In contrast, QlikView, I get most (not all ...) records with leading zeros. The field is defined in Oracle with the format VARCHAR2 (10 CHAR). The Oracle DB is on 11.2.0.1.0.

I assume that the manufacturer fills the fields are not clean. But how is it that I with QV not get the same result as with the SQL Developer? Can I change in QV? When the data is an indication coloring (coloring the 00 so is justified 🙂

The result with the Oracle SQL Developer looks like this:

SQL statement:

Select ekstpcol

from ekstpos

Result:

00

20

30

50

00

20

21

21

25

00

21

The result with QV looks like this:

Select statement:

SQL SELECT

EKSTPCOL

FROM EKSTPOS

Result:

0000

0020

0030

0050

0000

0020

0021

0021

25

0000

0021

Thank you

Christoph Geiser

[MarcoWedel]

Hallo Christoph,

one possible solution could be the num function to format your numbers:

tabEKSTPOS:
LOAD *,
    Num(EKSTPCOL,'00') as EKSTPCOL2
Inline [
EKSTPCOL
0000
0020
0030
0050
0000
0020
0021
25
2
10
012
0014
00016
];

hope this helps

regards

Marco

[#6]

Hallo Marco

Thanks for your input. The colorit no. may be numeric or string. Also they have different length. So I cannot work with a fix formatting.

Now I understand a little more, what happens even though I don't know, why QV works like this:

I got datasets with the colorit '21' and other datasets with the colorit '0021' (in a different Customer (Mandant). In this case, QV formats the content for all records to '0021'.

In the firs script I excluded Mandant 60:

The result:

E.g. the colorit 20 exists in Mandant 60 as '0020', so QV makes all of it as '0020'. But not so the colorit '25'.

I also tried to format the string with 'to_char(EKSTPCOL) as TEST_char'. Same result.

So by now I will split the dataload to our customer (Mandant), since the problem do not happen when I select only the required Mandant.

Any idea why QV works like this?

Regards Christoph

[Peter_Cammaert · Accepted solution]

'Speed' and 'symbol tables'. If you do not force your input to be text-only (Gysbert's suggestion is the best), QV will store all numerical values in a symbol table, but will store only one string representation for every individual numerical value. Usually, this is the first representation that is read from your datastream. All subsequent string representations will be thrown away. Maybe that explains the behavior you are experiencing at the moment.

The text-only treatment will serve you best. This is often used for fields that contain product codes (i.e. 00002 is a different product than 002). But it seems to me that you have to propagate this text-only treatment through your entire load script, or QV will intervene on the first occasion and try to revert seemingly numerical values back to real numerical values.

Peter

[#8]

Dear Peter

So I tried the suggestion from Gysbert again. I'm afraid that before I didn't really understood what he tries to say me .

But really, it works like that:

Result:

So, now I will adjust the script accordingly.

Best Regards

Christoph