Load only specifik number values from tabel

[vilstrup]

Hi,

i am struggling with a simple criterium for the loading process in the script.

I have a column with a lot of different numbers but i only want to load the lines with the numbers as: "910-350"

How do i write lets say "number, number, number, - , number, number, number" ?

Thank you in advance

- Nicolai

[Frank_Hartmann]

You could try like that:

1:
LOAD * INLINE [
    Data
    910-350
    914-350
    910-380
    930-320
    abc-def
    hij-klm
    365568
    jkasdcjk
    9928-22
];

NoConcatenate

2:
Load *,
if(len(KeepChar(subfield(Data,'-',1),'0123456789'))=3 and len(KeepChar(subfield(Data,'-',2),'0123456789'))=3,Data,0) as NewData
Resident 1 where if(len(KeepChar(subfield(Data,'-',1),'0123456789'))=3 and len(KeepChar(subfield(Data,'-',2),'0123456789'))=3,Data,0)<>0; DROP Table 1;

hope this helps

[Peter_Cammaert]

A more general solution can be created by implementing regular expression functions. Although it's a tricky thing to use macros in an AccessPoint client, they can be used without any hassle in your load script.

See here: http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

Best,

Peter

[MarcoWedel]

Hi,

maybe helpful:

Please help me get the string pattern of each field.

Finding text patterns in Data Columns

regards

Marco

[vilstrup]

Hi Frank, it seems right when i open your attached file, but i cant seem to make it work in my script

LOAD

ITEMID;

SQL SELECT *
FROM Ax2009Extract.dbo.INVENTTRANS

Where INVENTTRANS.ITEMID

if(len(KeepChar(subfield(ITEMID,'-',1),'0123456789'))=3 and len(KeepChar(subfield(ITEMID,'-',2),'0123456789'))=3,ITEMID,0) as NewData

Resident 1 where if(len(KeepChar(subfield(ITEMID,'-',1),'0123456789'))=3 and len(KeepChar(subfield(ITEMID,'-',2),'0123456789'))=3,ITEMID,0)<>0; DROP Table 1

; 

I'm new to qlikview so i think there is a bunch of things i'm missing. It says that subfield and keepchar is not a recognized built-in function name?

[Frank_Hartmann]

I´m not sure about your sql load, but what happens if you use the formula in frontend?

[Frank_Hartmann]

May be something like that:

tmp1:

LOAD

ITEMID;

SQL SELECT *
FROM Ax2009Extract.dbo.INVENTTRANS

noconcatenate

tmp2:

Load *,

if(len(KeepChar(subfield(ITEMID,'-',1),'0123456789'))=3 and len(KeepChar(subfield(ITEMID,'-',2),'0123456789'))=3,ITEMID,0) as NewData

Resident tmp1 where if(len(KeepChar(subfield(ITEMID,'-',1),'0123456789'))=3 and len(KeepChar(subfield(ITEMID,'-',2),'0123456789'))=3,ITEMID,0)<>0; drop table tmp1;

[vilstrup]

Hi Frank,

thank you very much for your effort to help. I ended up with a simple solution that seems to have done the trick.

In SQL SELECT *

Where INVENTTRANS.ITEMID like '___-___'

;