topic Re: How to capture whithout space data. in QlikView

How to capture whithout space data.

Wed, 27 Jan 2016 13:40:52 GMT

Hi Community,

Please refere below data sample -

230

123

455

555

645

222

i only want those data that i have no space so which function i have to used in script level.

my expected output is

230

455

645

222

Thanks in advance

Re: How to capture whithout space data.

MK_QSL — Wed, 27 Jan 2016 13:42:56 GMT

Trim(id) as id

Updated : I misunderstood your requirement...! LOL

Re: How to capture whithout space data.

sunny_talwar — Wed, 27 Jan 2016 13:44:58 GMT

May be make use of this SET statement

SET Verbatim = 1;

and then check the field for Space -> Where Len(Trim(KeepChar(FieldName, ' '))) = 0; Where Len(KeepChar(FieldName, ' ')) = 0;

Re: How to capture whithout space data.

settu_periasamy — Wed, 27 Jan 2016 13:46:56 GMT

May be

Load id from source where istext (id);

Re: How to capture whithout space data.

sunny_talwar — Wed, 27 Jan 2016 13:47:32 GMT

Sample attached:

SET Verbatim = 1;

Table:

LOAD id

FROM

Community_203223.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(KeepChar(id, ' ')) = 0;

Re: How to capture whithout space data.

Anonymous — Wed, 27 Jan 2016 13:47:50 GMT

may be like this at script?

if(left(ID,1)<>' ', ID) as ID

Re: How to capture whithout space data.

sunny_talwar — Wed, 27 Jan 2016 13:50:16 GMT

Actually you might not even need SET Verbatim = 1;

Table:

LOAD id

FROM

Community_203223.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(KeepChar(id, ' ')) = 0;

Re: How to capture whithout space data.

Anonymous — Wed, 27 Jan 2016 13:55:00 GMT

MRKachhiaIMP

I think it will remove spaces and display the values as well.

But he dont want to display those values, If I understood it correctly

Re: How to capture whithout space data.

HirisH_V7 — Wed, 27 Jan 2016 14:28:46 GMT

Hi,

like this,

SAMPLE:
LOAD ID,
rtrim(ltrim(ID)) as ID1
FROM
[Trim Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

output:

PFA,

Hirish

Re: How to capture whithout space data.

anlonghi2 — Wed, 27 Jan 2016 14:58:59 GMT

Hi Ishwar,

another solution could be:

set Verbatim=1;

LOAD id

FROM

example.xlsx

(ooxml, embedded labels, table is Sheet3) where SubStringCount(id,' ')=0;

Best regards

Andrea

Re: How to capture whithout space data.

MarcoWedel — Wed, 27 Jan 2016 16:48:50 GMT

Set Verbatim = 1;

LOAD id
From YourSource
Where Len(Trim(id)) = Len(id);

regards

Marco

Re: How to capture whithout space data.

HirisH_V7 — Wed, 27 Jan 2016 16:58:09 GMT

hi MarcoWedel‌,

please explain me what does exactly the Variable !!... Verbatim do?

-Hirish

Re: How to capture whithout space data.

sunny_talwar — Wed, 27 Jan 2016 17:00:15 GMT

From QlikView's help (QlikView ‒ Verbatim)

Re: How to capture whithout space data.

Thu, 28 Jan 2016 06:11:47 GMT

HI friends,

Thank u for your quik reply,

i used if() and len() function in my script i got solution of my problem.

please refer below attached extractor

Re: How to capture whithout space data.

anat — Thu, 28 Jan 2016 06:28:59 GMT

Hi Khutal,

in case if we have 4 digit number in ID column like 9999..... ,then how it will work?

Re: How to capture whithout space data.

rajeshforqlikvi — Thu, 28 Jan 2016 06:36:25 GMT

Hi you can you trim() function also.

Re: How to capture whithout space data.

Thu, 28 Jan 2016 10:11:09 GMT

Hi Ananth,

In my case i have only 3 digit data and i want only those 3 digit data those data have not a space that i mention in my expected answer.

Hi Rajesh,

I have also used Trim() function but i didn't got my expecated answer.

Thanks to both of you.

Re: How to capture whithout space data.

anlonghi2 — Thu, 28 Jan 2016 18:11:10 GMT

Hi Ishwar,

try the following instruction in your script.

...

LOAD

if(isnum(replace(id,' ','A')),id ) as id

FROM

...

best regards

Andrea