How to delete the combination of Character and number records from the field

[rkpatelqlikview]

Hi Experts,

Please suggest on the below scenario.

Please find the attached sample excel data

I tried with multiple functions to remove the records from the field.

I want to remove the codes which are combination of number and character records.

Original code size is '9'

In below example removed records from 10201130MH to NE09. Thanks in advance.

[isingh30]

You don't need these records at all? If yes, then we can ignore these at the time of loading data.

Thanks.

[passionate]

PFA, Solution.

[rkpatelqlikview]

Yes Ishtdeep, I don't want these records, Just i want records like started with 9.

[rkpatelqlikview]

Thanks Pankaj,

Here actually i don't want those records. I want to remove those records which is having combination with numbers and characters  completely from the fields. I want below marked data.

[isingh30]

No need to load these records then.

Thanks.

[shiveshsingh]

But why not NE04, NE09, they are also combination of chars and numbers?

[rkpatelqlikview]

Thanks Shive

Yes.. Wherever we find the combination of both chars and number we can remove. Including NE04, NE09 also.

we can remove

[Peter_Cammaert]

If I put your requirement like this: "Load all rows for which the first 9 characters of field [Carrier Code] are digits", would that be ok?

Then add something like this to your script:

LOAD ...

FROM ...

WHERE (Len(PurgeChar(Left([Carrier Code], 9), '0123456789')) = 0);

You can add additional validations to the WHERE clause, like Left([Carrier Code], 1) = '9'

Best,

Peter

[MarcoWedel · Accepted solution]

Hi,

one solution might be also:

table1:
LOAD Source,
    If(Source like '9*' and IsNum(Left(Source,9)),Left(Source,9)) as Required
FROM [https://community.qlik.com/servlet/JiveServlet/download/1489043-326111/sampledata.xls] (biff, embedded labels, table is Sheet1$);

hope this helps

regards

Marco