4 Replies Latest reply: Feb 9, 2018 12:55 AM by Kevin Somosera RSS

    Field <fieldname> not found Error

    Kevin Somosera

      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!

        • Re: Field <fieldname> not found Error
          Ali Hijazi

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

          • Re: Field <fieldname> not found Error
            Kaushik Solanki

            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

            • Re: Field <fieldname> not found Error
              max payne

              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,

              • Re: Field <fieldname> not found Error
                Kevin Somosera

                Thanks everyone for your help!