topic Re: Where Statment in QlikView

Where Statment

Wed, 27 Nov 2013 10:26:22 GMT

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,

Re: Where Statment

kaushiknsolanki — Wed, 27 Nov 2013 10:35:38 GMT

Hi,

Try this.

Centre:

LOAD OrgLocation as Location,

Centre,

Delivery,

Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where Wildmatch(Delivery,'InHouse');

Centre:

LOAD OrgLocation as Location,

Centre,

Delivery,

Region

FROM

Centres.xlsx

(ooxml, embedded labels, table is Centre)

Where Delivery = 'InHouse';

Regards,

Kaushik Solanki

Re: Where Statment

salto — Wed, 27 Nov 2013 10:35:42 GMT

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.

Re: Where Statment

VishalWaghole — Wed, 27 Nov 2013 10:35:59 GMT

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

Re: Where Statment

Wed, 27 Nov 2013 10:45:18 GMT

Thanks Vishal,

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

Re: Where Statment

Wed, 27 Nov 2013 10:46:38 GMT

Hi Salto,

Will try your solution seems interesting,

Thanks

Re: Where Statment

kalyandg — Wed, 27 Nov 2013 10:46:48 GMT

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

Re: Where Statment

Wed, 27 Nov 2013 10:47:46 GMT

Hi Kaushik,

Tried your answer and is a great solution.

Thanks,

Re: Where Statment

VishalWaghole — Wed, 27 Nov 2013 10:48:39 GMT

Yes You are exactly right.

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

Thanks and Regards,

Vishal Waghole

Re: Where Statment

Wed, 27 Nov 2013 10:50:32 GMT

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,