topic Re: Load only specifik number values from tabel in QlikView

Load only specifik number values from tabel

vilstrup — Tue, 16 May 2017 13:04:56 GMT

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

Re: Load only specifik number values from tabel

Frank_Hartmann — Tue, 16 May 2017 16:07:09 GMT

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

Re: Load only specifik number values from tabel

Peter_Cammaert — Tue, 16 May 2017 16:14:52 GMT

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

Re: Load only specifik number values from tabel

MarcoWedel — Tue, 16 May 2017 22:19:10 GMT

Hi,

maybe helpful:

Please help me get the string pattern of each field.

Finding text patterns in Data Columns

regards

Marco

Re: Load only specifik number values from tabel

vilstrup — Wed, 17 May 2017 08:28:48 GMT

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?

Re: Load only specifik number values from tabel

Frank_Hartmann — Wed, 17 May 2017 08:51:29 GMT

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

Re: Load only specifik number values from tabel

Frank_Hartmann — Wed, 17 May 2017 09:02:39 GMT

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;

Re: Load only specifik number values from tabel

vilstrup — Thu, 18 May 2017 06:47:56 GMT

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 '___-___'

;