topic Re: load where value begins with in QlikView

load where value begins with

Wed, 09 Jan 2013 13:10:20 GMT

Hi there,

I'm brand new to Qlikview so please be gentle!

I'm loading data from an OLE DB connection and only want to load certain values. I've managed to use:

where "scj_ayrc" >= '2008/9' which works fine, but I now want to add in where a different field has values that begin with a range of letters. I was expecting to be able to use where "field name" like 'B%' as a basic start but had a garbage error message. I want both the year statement and where another field starts with B, P or R.

Can anyone point out where I'm going wrong?

thanks very much

Rosey

Re: load where value begins with

Anonymous — Wed, 09 Jan 2013 13:14:54 GMT

Rosey,

It will be something liike this:

...
WHERE "scj_ayrc" >= '2008/9'
AND ("field name" like 'B%'
OR "field name" like 'P%'
OR "field name" like 'R%')

Regards,
Michael

Re: load where value begins with

Wed, 09 Jan 2013 13:22:03 GMT

Thanks Michael,

I've tried that but I'm just geting zero lines fetched even thought there are definitely matching values...

Rosey

Re: load where value begins with

Anonymous — Wed, 09 Jan 2013 13:26:00 GMT

Rosey,

I'm more suspicious about the 1st part. Try

WHERE 1=1

AND ("field name" like 'B%'

OR "field name" like 'P%'

OR "field name" like 'R%')

WHERE "scj_ayrc" >= '2008/9'

AND 1=1

See whcih one returns values.

Regards,

Michael

Re: load where value begins with

Wed, 09 Jan 2013 13:38:04 GMT

Hi Michael,

It's the second I'm afraid! Even if I take out the year based statement, even where "scj_crsc" like 'B%' doesn't return any values, although where "scj_crsc" = 'BLAWPRM' does...

Thanks

Rosey

Re: load where value begins with

flipside — Wed, 09 Jan 2013 13:44:45 GMT

What are you loading data from - SQL Server? MS Access?

In any case, try this option ...

... WHERE LEFT([field name],1) IN ('B','P','R');

flipside

Re: load where value begins with

Anonymous — Wed, 09 Jan 2013 13:44:45 GMT

Rosey,

Are you running this in SELECT or in LOAD? My understanding untill now was that it was select from a database. If it is in load, the syntax is different. Instead

("field name" like 'B%'

OR "field name" like 'P%'

OR "field name" like 'R%')

you can use:

wildmatch("field name", 'B*','P*','R*')

Regards,

Michael

Re: load where value begins with

Wed, 09 Jan 2013 13:51:23 GMT

Thanks Michael - sorry should have been clearer.

That's worked perfectly now!

Rosey