How to seperate Text from an Integer coloumn - Qlik Community

Report Inappropriate Content · ‎2012-01-13

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

mphekin12 · ‎2012-01-13

You could try this in your load script:

load

Store_ID,

Question,

Answer

where isnum(Store_ID)=-1

;

jafari_ervin · ‎2012-01-13

hi

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

Sokkorn · ‎2012-01-13

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

disqr_rm · ‎2012-01-13

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

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

vijay_iitkgp · ‎2012-01-16

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);