topic How to seperate Text from an Integer coloumn in QlikView

How to seperate Text from an Integer coloumn

Fri, 13 Jan 2012 19:19:40 GMT

Hi, I am a Beginner in Qlikview.Please help me with the below. Thank You My Source Data is as below:

Store_ID Question Answer

100 1 Y

101 2 N

100-a 1 Y

100-b 2 N

In the script, I want to input only valid integer Store_ID's and ignore the ones which have 'a' and 'b' in them.

Store_ID Question Answer

100 1 Y

101 2 N

SKandregula

How to seperate Text from an Integer coloumn

mphekin12 — Fri, 13 Jan 2012 20:08:11 GMT

You could try this in your load script:

load

Store_ID,

Question,

Answer

where isnum(Store_ID)=-1

;

How to seperate Text from an Integer coloumn

jafari_ervin — Fri, 13 Jan 2012 23:03:29 GMT

if you know how many chars are numbers you can split them with left and write fuction,

for example you have this format 123-a you can select your number like this

left(Store_ID , 3)

the result is 123

best regards,

Ervin

Re: How to seperate Text from an Integer coloumn

Sokkorn — Sat, 14 Jan 2012 01:08:28 GMT

Hi,

Let look around my script below

[Data]:
LOAD * INLINE [
Store_ID,             Question,           Answer
100,                     1,                       Y
101,                     2,                       N
100-a,                  1 ,                      Y
100-b,                  2 ,                      N ];
QUALIFY *;
[Data2]:
LOAD DISTINCT
    Replace(KeepChar(Store_ID,'-0123456789'),'-','')    AS Store_ID,
    Question,
    Answer
RESIDENT [Data];
[Data3]:
LOAD
    Store_ID,
    Question,
    Answer
RESIDENT [Data] WHERE IsNum(KeepChar(Store_ID,'0123456789-'));

I thinks table [Data3] is the one you looking for.

Hope this help.

Regards,

Sokkorn Cheav

Re: How to seperate Text from an Integer coloumn

disqr_rm — Sat, 14 Jan 2012 01:36:16 GMT

This could be a good usage for "subfield" function as well.

subfield(Store_ID, '-', 1) AS Store_ID

How to seperate Text from an Integer coloumn

vijay_iitkgp — Mon, 16 Jan 2012 07:52:22 GMT

Hi Rakesh,

By using subfield he will get 4 rows but he wants to delete rows with any non numeric store id.

simpler approach :

load

Store_ID,

Question,

Answer

where where not isnull(num(Store_Id);