Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
kris_vliegen
Contributor II

Insert long field from AS400 (iseries)

Hello,

I try to import a field (length = 245 characters) from an AS400 (iseries) database. I only see the first 62 characters in Qlikview.

After the 62 characters there are 14 blanco characters and after those blanco's we have textcharacters. But these textcharacters are not in Qlikview.

Is it possible that Qlikview cut off the field after a number of blanco characters?

Or how can I insert all the 245 characters in Qlikview?

Regards,

Kris

Tags (3)
1 Solution

Accepted Solutions
dirk_konings
Contributor III

Re: Insert long field from AS400 (iseries)

No, now you do the split in the QV section of the script.

You have to do it in the SQL Select part

something like this

Load

...

...

;

SQL Select mid(CPFLD1,92,15) as ShortName, mid(XPFLD1, 84, 3) as Type from X62FIL700O.PXP

where ...

9 Replies
dirk_konings
Contributor III

Re: Insert long field from AS400 (iseries)

Kris,

Do you get all characters when you load this data in an other application (like Excel) when you conect via ODBC? Maybe it's cut-off by the ODBC driver ?

Regards,

Dirk

kris_vliegen
Contributor II

Re: Insert long field from AS400 (iseries)

Dirk,

In Excel I see the full field (more than 62 characters). So I don't think it is the ODBC-Driver...

Regards,

Kris

dirk_konings
Contributor III

Re: Insert long field from AS400 (iseries)

Kris,

Okay,

I know Qlikview removes trelling spaces but i don't know after how many digits.

Have you tried to extract an other field with the same length and fully filled with characters?

What is the length of the field / value in QV? (length(fielda)) Mayby a special character in it, like a CR (Carriage Return)? You wont see the data in a listbox, but could see it in a textbox.

Dirk

kris_vliegen
Contributor II

Re: Insert long field from AS400 (iseries)

Dirk,

I've been looking in the datadescription of the file. And the value of the field is defined by a data-structure.

In this data-structure there is a packed field. And it seems that he cut-off the field at the beginning of the packed value.

Could this be related to the driver?

Regards,

Kris

kris_vliegen
Contributor II

Re: Insert long field from AS400 (iseries)

This doesn't give a difference.

dirk_konings
Contributor III

Re: Insert long field from AS400 (iseries)

Could you just split the field in your SQL SELECT statement at the position the PackedDecimal field starts?

This may help. Maybe convert the 2nd part to a integer of varchar

kris_vliegen
Contributor II

Re: Insert long field from AS400 (iseries)

Do you mean by this?

PXPSS:

LOAD XPDELC,

    XPKEYF,

    XPFLD1,

    mid(XPFLD1, 92, 15) as ShortName,

    mid(XPFLD1, 84, 3) as Type;

SQL SELECT *

FROM S060AE2T.X62FIL700O.PXP where Left(XPKEYF,2) = 'SS' ;

Because there are no values in ShortName and Type.

dirk_konings
Contributor III

Re: Insert long field from AS400 (iseries)

No, now you do the split in the QV section of the script.

You have to do it in the SQL Select part

something like this

Load

...

...

;

SQL Select mid(CPFLD1,92,15) as ShortName, mid(XPFLD1, 84, 3) as Type from X62FIL700O.PXP

where ...

kris_vliegen
Contributor II

Re: Insert long field from AS400 (iseries)

Thanks Dirk!

This is working.

We only need to use "substr" because the AS400 doesn't understand the "mid"

Regards,

Kris

Community Browser