topic Re: Need only Integer values in QlikView

Need only Integer values

dmohanty — Mon, 03 Feb 2014 12:29:14 GMT

Hi All,

The requirement is quite simple, but some how its not working. Could you please help a bit?

I have a Field called COLUMN with values (ABC1, DEF1, 12345678, 87654321)

I need the output with 6 chars like COLUMN(123456,876543)

Also, I have right spaces besides ABC1 and DEF1. Need to remove the spaces as well.

Re: Need only Integer values

sundarakumar — Mon, 03 Feb 2014 12:41:06 GMT

Hi,

Rtrim(COLLUMN) will remove the right spaces.

Left(COLUMN,6) will trim it to six charecters.

Sundar

Re: Need only Integer values

sundarakumar — Mon, 03 Feb 2014 12:42:32 GMT

In your case it should be

Left(Rtrim(COLUMN),6)

Please use the above function in the script itself.

-Sundar

Re: Need only Integer values

dmohanty — Mon, 03 Feb 2014 12:46:19 GMT

Thanks. I tried that too, but I am not sure why I am missing the Integer values.

I am getting only ABC1, DEF1, whereas I need 123456,876543.

Don't know why!

Re: Need only Integer values

maxgro — Mon, 03 Feb 2014 12:49:01 GMT

Tmp:

load

left(trim(Field),6) as Field1

where IsNum(Field);

LOAD * INLINE [

Field

ABC1

DEF1

12345678

87654321

];

Re: Need only Integer values

sundarakumar — Mon, 03 Feb 2014 12:49:32 GMT

Please share a sample app.

Where are you using this formula?in the edit script?

Re: Need only Integer values

Mon, 03 Feb 2014 13:00:02 GMT

Hi,

Try this:

IF(LEN(FIELD) = 6, NUM(FIELD))

Re: Need only Integer values

maxgro — Mon, 03 Feb 2014 13:02:05 GMT

or if you have just one row (ABC1, DEF1, 12345678, 87654321)

LOAD concat(left(trim(Field10), 6), ',') as Field10New WHERE isnum(subfield(Field10, ','));

LOAD subfield(purgechar(Field10, '()'), ',') as Field10;

LOAD * INLINE [

Field10

"(ABC1, DEF1, 12345678, 87654321)"

];