Field <fieldname> not found Error

[ksomosera10]

Hi,

I'm having trouble on Qliksens script all day.

I always get the Field <fieldname> not found error.

Here's the script:

LOAD

    [Region] & '-' & [Country] & '-' & [Brand] & '-' & [Media] as [Key]

    ,Region

    ,Country

    ,Brand

    ,Media

    ,Date(Floor(num("Start Date")),'MM/DD/YYYY') as [StartDate]

    ,Date("End Date") as [EndDate]

FROM [lib://sample.xlsx]

(ooxml, embedded labels, table is sheet1)

Where [Key] <> '---';

I always get the

Field 'Key' not found

can somebody help me out here?

Thanks!

[ali_hijazi · Accepted solution]

Key is not loaded yet when you use it in the where condition

you need to write the following:

where [Region] & '-' & [Country] & '-' & [Brand] & '-' & [Media] <> '---'

or you may use preceding load as follows:

Load * where Key <> '---';

LOAD

    [Region] & '-' & [Country] & '-' & [Brand] & '-' & [Media] as [Key]

    ,Region

    ,Country

    ,Brand

    ,Media

    ,Date(Floor(num("Start Date")),'MM/DD/YYYY') as [StartDate]

    ,Date("End Date") as [EndDate]

FROM [lib://sample.xlsx]

(ooxml, embedded labels, table is sheet1);

[kaushiknsolanki]

Hi,

You can not refer the calculated field created in same load statement in where clause.

Try this.

Load * Where [Key] <> '---';

LOAD

    [Region] & '-' & [Country] & '-' & [Brand] & '-' & [Media] as [Key]

    ,Region

    ,Country

    ,Brand

    ,Media

    ,Date(Floor(num("Start Date")),'MM/DD/YYYY') as [StartDate]

    ,Date("End Date") as [EndDate]

FROM [lib://sample.xlsx]

(ooxml, embedded labels, table is sheet1);

Regards,

Kaushik Solanki

[big_dreams]

Hi,

As other suggested You can not use derived field in same Load where clause.

There are 2 ways to use it.

1: using Preceding load

     > which is already suggested by other 2 people

2: Using Resident Load

     > try below

Temp:

/*******

Your Current Script

*****/

NoConcatenate

Load * from Temp

where [Key] <> '---';

drop table Temp;

Well, 1st method gives best solution compare to 2nd.

Regards,

[ksomosera10]

Thanks everyone for your help!