Where Statment

[#1]

Hi,

Trying to set the options for a list box from a table that I have loaded, but seems that i cannot come up with the WHERE clause. The table contains a list of Locations (Centres) and in another column I have a "Delivery" which can either be "InHouse" or "OutSource" so the idea is for ONLY the InHouse centres to be visible, so I have wrote the following:

Centre:

LOAD OrgLocation as Location,

     Centre,

     Delivery,

     Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where (Delivery) = InHouse;

but when I reload I get the foolowing message:

Field not found - <InHouse>

Centre:

LOAD OrgLocation as Location,

     Centre,

     Delivery,

     Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where (Delivery) = InHouse

What am I doing wrong?

Any help and All help are very much appreciated.

Thanks in advance,

[kaushiknsolanki · Accepted solution]

Hi,

     Try this.

Centre:

LOAD OrgLocation as Location,

     Centre,

     Delivery,

     Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where Wildmatch(Delivery,'InHouse');

or

Centre:

LOAD OrgLocation as Location,

     Centre,

     Delivery,

     Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where Delivery = 'InHouse';

Regards,

Kaushik Solanki

[salto]

Hi,

I would do the next: first load the Excel in a Temporary table and then drop all fields different from InHouse from the temporary table:

Temp_Centre:

LOAD OrgLocation as Location,

     Centre,

     Delivery,

     Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre);

Centre:

load * resident Temp_Centre where Delivery ='InHouse';

drop table Temp_Centre;

Hope this helps.

[VishalWaghole]

Hi Vitor,

Try you where clause like this :

Centre:

LOAD OrgLocation as Location,

     Centre,

     Delivery,

     Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where  match(Delivery,InHouse)

Thanks and Regards,

Vishal Waghole

[#5]

Thanks Vishal,

But I think we need to add single quotes (') before and after 'InHouse'... like works perfectly.

[#6]

Hi Salto,

Will try your solution seems interesting,

Thanks

[kalyandg]

hi,

try as the below

Centre:

LOAD OrgLocation as Location,

     Centre,

     Delivery,

     Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where (Delivery) = 'InHouse';

Field values must given in single quote

Thanks,

Kalyan

[#8]

Hi Kaushik,

Tried your answer and is a great solution.

Thanks,

[VishalWaghole]

Yes You are exactly right.

I just forgot to add single quotes ('InHouse')

Thanks and Regards,

Vishal Waghole

[#10]

Thanks Kalyan,

Not sure why didn't worked for me at the first time, as I try this statement before posting on the forum .

Cheers,