Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ksomosera10
Creator II
Creator II

Field <fieldname> not found Error

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!

1 Solution

Accepted Solutions
ali_hijazi
Partner - Master II
Partner - Master II

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);

I can walk on water when it freezes

View solution in original post

4 Replies
ali_hijazi
Partner - Master II
Partner - Master II

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);

I can walk on water when it freezes
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
big_dreams
Creator III
Creator III

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
Creator II
Creator II
Author

Thanks everyone for your help!