10 Replies Latest reply: Dec 5, 2017 4:53 PM by Mona Houcheime RSS

    Troubleshooting 'Field not Found - <My Field

    Mona Houcheime

      Hi QV Community!

       

      I'm new to QV and exploring my way around. I've learned several tricks so far and I'm hoping someone can help me with a minor issue.

       

      Twice now, after I've created a new field in one of a few tables I've loaded and attempted to re-format or include in downstream calculations, I get the Script Error: 'Field Not Found - <My Field>.

       

      The examples are

      • The first new field that I created was a result of a mapped item and formatted as a date (Renewed On 2). When I attempted to convert this to fiscal quarter with this code, the script failed:
        • Key_Table:

        • Mapping LOAD [Old Entitlement Key] as [Entitlement Key],

        •      [Sales Order Date]

        • FROM ....excel file


          All_Data:

        • LOAD [Bill To],

        •      [End User],

               [Old Entitlement Key],

        •      [Item: Name],

        •      [Entitlement Key],

        •      ApplyMap('Key_Table',[Entitlement Key],'') as [Renewed On 2],

          Year([Renewed On 2])&'-'&'Q'& Ceil(Month([Renewed On 2])/3) as [Renewed On Quarter 3]

          FROM ....excel file


      • The second attempt was incorporating the newly calculated field (Actual duration) into script in order to create various categories in years as a new field:
        • ([End Date]-[Start Date])/365 as [Actual Duration],
        • IF([Actual Duration]<1.5,"1 Year",IF([Actual Duration]<2.5,"2 Years",IF([Actual Duration]<3.5,"3 Years",IF([Actual Duration]<4.5,"4 Years",IF([Actual Duration]<5.5,"5 Years",IF([Actual Duration]>5.5,"> 6 Years")))))) as term

       

      And here are my failed attempts for resolution are:

      • Changing data format to date or number respectively
      • Forcing date change using Makedate function
      • Joining tables

       

      Any recommendations or insights would be appreciated!!

      Thanks QV community!

        • Re: Troubleshooting 'Field not Found - <My Field
          Luis Madriz

          Hi,

           

          You can't use the calculated field within the same pass. Instead of using [Renewed on 2], keep using the mapping.. Same thing for Actual Duration...

           

          Or, make another pass (preceding load) use the calculated fields,

           

          I hope this helps,

           

          Cheers,

           

          Luis

          • Re: Troubleshooting 'Field not Found - <My Field
            Luis Madriz

            No problem Mona, I keep learning every day,

             

            This is what I mean. The first pass happens closer to where the "FROM [C\Us... " is, and it's here when [Renewed on 2] and [Actual Duration] are created, then, in the second pass which is above (this took me a while to understand when I started with Qlik few months ago), is when you load everything if you want, thus the * and add the new calculated fields [Renewed On 3], term, etc,


            Of course you could have done all in one pass as you did with the mapping. The decision may come down to performance, number of records, maintainability, elegancy... etc.

             

            Please review the idea and happy to discuss if you have more questions

             

            Cheers,

             

            Luis

             

             

            All_Data:

            LOAD *,

                 Year([Renewed On 2])&'-'&'Q'& Ceil(Month([Renewed On 2])/3) as [Renewed On 3],

                 Year([Renewed On 2])&'-'&'Q'& Ceil(Month([Renewed On 2])/3) as [Renewed On Quarter 3],

                 IF([Actual Duration])<1.5,'1 Year',IF([Actual Duration])<2.5,'2 Years',IF([Actual Duration])<3.5,'3 Years',IF([Actual Duration])<4.5,'4 Years',IF([Actual Duration])<5.5,'5 Years',IF([Actual Duration])>5.5,'> 6 Years')))))) as term;

             

            LOAD [Bill To],

                 [End User],

                 [Item: Name],

                 Quantity,

                 [Amount (Net)],

                 [Old Entitlement Key],

                 [Sales Order Date],

                 [Start Date],

                 [End Date],

                 [Entitlement Key],

                 [License Type],

                 Region,

                 [Renewed on],

                 Year([End Date])&'-'&'Q'& Ceil(Month([End Date])/3) as [Renewal Quarter 3],

                 Year([Sales Order Date])&'-'&'Q'& Ceil(Month([Sales Order Date])/3) as [Sales Order Quarter 3],

                 ApplyMap('Key_Table',[Entitlement Key],'') as [Renewed On 2],

                 ([End Date]-[Start Date])/365 as [Actual Duration]

             

            FROM

            [C:\Us...