Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ahmonah2017
New Contributor II

Troubleshooting 'Field not Found - <My Field

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!

1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: Troubleshooting 'Field not Found - <My Field

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

10 Replies
luismadriz
Valued Contributor

Re: Troubleshooting 'Field not Found - <My Field

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

ahmonah2017
New Contributor II

Re: Troubleshooting 'Field not Found - <My Field

Thanks Luis!

It worked! I had to plug in the entire formula:

[End Date]-[Start Date])/365

However not sure what you mean about the same/another pass.

Does this mean that I would start an other load statement by introducing the value as the same value:

ie: Load [Actual Duration] as [Actual Duration]

Or reintroduce :  ApplyMap('Key_Table',[Entitlement Key],'') as [Renewed On 2],  in the next calculation.

Can you clarify how one can redefine a newly created field and then use in future calculations?

Thanks again Luis!!

luismadriz
Valued Contributor

Re: Troubleshooting 'Field not Found - <My Field

No problem,

Please remember to mark as Correct and Helpful

For example:

Load

*,

c*10 as d;

Load

a,

b,

a+b as C

from ...;

Well, evemn better if you check this one: Loading data from a previously loaded table ‒ QlikView

Cheers,

ahmonah2017
New Contributor II

Re: Troubleshooting 'Field not Found - <My Field

Of course! Just did.

Thank you!

luismadriz
Valued Contributor

Re: Troubleshooting 'Field not Found - <My Field

Thanks Mona,

Please mark one of the replies as Correct.... The green star, that way the thread will be resolved,

Regards,

Luis

ahmonah2017
New Contributor II

Re: Troubleshooting 'Field not Found - <My Field

Thanks Luis.

Unfortunately I have not been able to resolve the field. The script error using the recommendation provided in here Loading data from a previously loaded table ‒ QlikView shows 'Table not found'.

Also, I'm not clear on the example you've shown above d.

how does d get used in calculations in your second load table?

luismadriz
Valued Contributor

Re: Troubleshooting 'Field not Found - <My Field

Hi Mona,

If you want, send me your script and I'll make the changes,

Regards,

Luis

ahmonah2017
New Contributor II

Re: Troubleshooting 'Field not Found - <My Field

Hello Luis,

Once again thanks for your help! I may have spent an entire day watching youtube videos, searching QV help and trying a few scripts...

The script that I'm working on is below and I've highlighted the problematic statements.

Key_Table:

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

     [Sales Order Date]

FROM

,

     [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],

     Year(ApplyMap('Key_Table',[Entitlement Key],''))&'-'&'Q'& Ceil(Month(ApplyMap('Key_Table',[Entitlement Key],''))/3) as [Renewed On 3],

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

     ([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

FROM

[C:\Us...

Again, I can't thank you enough. I wish that I can send you extra points if it's of any help at all! Let me know.

Have an awesome day!

luismadriz
Valued Contributor

Re: Troubleshooting 'Field not Found - <My Field

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...