load where value begins with

[#1]

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

[Anonymous]

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

[#3]

Thanks Michael,

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

Rosey

[Anonymous]

Rosey,

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

1

WHERE 1=1

AND ("field name" like 'B%'

  OR "field name" like 'P%'

  OR "field name" like 'R%')

2

WHERE "scj_ayrc" >= '2008/9'

AND 1=1

See whcih one returns values.

Regards,

Michael

[#5]

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

[flipside]

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

[Anonymous · Accepted solution]

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

[#8]

Thanks Michael - sorry should have been clearer.

That's worked perfectly now!

Rosey