Field not found error even though it exists.

kathan27 — Sat, 16 Nov 2024 20:17:14 GMT

I have run this script without any errors before. There is literally no change but it is giving me error - field not found 'TOTAL VALUE EUR', 'TOTAL VALUE USD', ... and so on.

Transformed_Detail:
NoConcatenate
LOAD
[Line #],
[Unit Qty],
[Net Weight (KG)] as [Net Weight (Kg)],
[Part Number] as [Equipment / Part Number],
[Description of Merchandise],
if(isnull([INVOICE CURRENCY]),
[Import
HTS]) as [Import HTS],
if(isnull([INVOICE CURRENCY]),[Export
HTS]) as [Export HTS],

[INVOICE CURRENCY],

[Import
HTS] as [Import HTS for Weight Report],

left(replace(replace([Import
HTS],'-',''),'.',''),8)
as [CN8 Code for Weight Report],
ECCN,
[US License Exception],
trim(subfield([Country of Origin],'-',1)) as [Country of Origin],

FileName,

if(isnull([INVOICE CURRENCY]),
if(not isnull([TOTAL VALUE EUR]),[TOTAL VALUE EUR]*$(vEURtoUSD),
if(not isnull([TOTAL VALUE GBP]),[TOTAL VALUE GBP]*$(vGBPtoUSD),
if(not isnull([TOTAL VALUE USD]),[TOTAL VALUE USD],
if(not isnull([TOTAL VALUE CAD]),[TOTAL VALUE CAD]*$(vCADtoUSD),
if(not isnull([TOTAL VALUE HKD]),[TOTAL VALUE HKD]*$(vHKDtoUSD),
if(not isnull([TOTAL VALUE AUD]),[TOTAL VALUE AUD]*$(vAUDtoUSD),
if(not isnull([TOTAL VALUE SGD]),[TOTAL VALUE SGD]*$(vSGDtoUSD),
if(not isnull([TOTAL VALUE MXN]),[TOTAL VALUE MXN]*$(vMXNtoUSD),
if(not isnull([TOTAL VALUE NOK]),[TOTAL VALUE NOK]*$(vNOKtoUSD),
if(not isnull([TOTAL VALUE KRW]),[TOTAL VALUE KRW]*$(vKRWtoUSD),
if(not isnull([TOTAL VALUE JPY]),[TOTAL VALUE JPY]*$(vJPYtoUSD),
)))))))))))
,
if([INVOICE CURRENCY]='USD',
[Total Value],[INVOICE CURRENCY])as [Invoice Value USD],

if(isnull([INVOICE CURRENCY]),
if(not isnull([TOTAL VALUE EUR]),[TOTAL VALUE EUR],
if(not isnull([TOTAL VALUE GBP]), ([TOTAL VALUE GBP]*$(vGBPtoUSD)/$(vEURtoUSD)),
if(not isnull([TOTAL VALUE USD]),([TOTAL VALUE USD]/$(vEURtoUSD)))
)),
if([INVOICE CURRENCY]='USD',(
[Total Value]/$(vEURtoUSD)),[INVOICE CURRENCY])) as [Invoice Value EUR],

if(isnull([INVOICE CURRENCY]),
if(not isnull([TOTAL VALUE EUR]),(([TOTAL VALUE EUR]*$(vEURtoUSD))/$(vGBPtoUSD)),
if(not isnull([TOTAL VALUE GBP]), [TOTAL VALUE GBP],
if(not isnull([TOTAL VALUE USD]),([TOTAL VALUE USD]/$(vGBPtoUSD)))
)),
if([INVOICE CURRENCY]='USD',(
[Total Value]/$(vGBPtoUSD)),[INVOICE CURRENCY]))
as [Invoice Value GBP],

if(isnull([INVOICE CURRENCY]),
if(not isnull([TOTAL VALUE EUR]),[TOTAL VALUE EUR],
if(not isnull([TOTAL VALUE GBP]), ([TOTAL VALUE GBP]/$(vEURtoGBP_German)*$(vEURtoUSD_German)),
if(not isnull([TOTAL VALUE USD]),([TOTAL VALUE USD]/$(vEURtoUSD_German)))
)),
if([INVOICE CURRENCY]='USD',(
[Total Value]/$(vEURtoUSD_German)),[INVOICE CURRENCY]))
as [Invoice Value EUR_German],

[Suppl. Unit] as [Supplemental Unit],
[PO / RMA #],
'Item Value' as [Value Type]
RESIDENT Detail;

DROP TABLES Header, Detail;

Re: Field not found error even though it exists.

AlecPtn — Fri, 09 Aug 2019 21:22:30 GMT

If you comment out this part of the load script and load just 'Detail' do you see those fields still?

If not I would recommend rewriting in the connection line for 'Detail' as I have had excel sheets act up on me sometimes when I connect to them.

Re: Field not found error even though it exists.

kathan27 — Fri, 09 Aug 2019 21:46:50 GMT

Yes I did that and the all the fields load without any error. The script fails when it tries to execute Transformed_Detail part.

Re: Field not found error even though it exists.

Brett_Bleess — Wed, 28 Aug 2019 13:12:41 GMT

Kathan, what would be most helpful to me is the actual script log with the failure as well as one where things are successful, so I can see what changes on the failure...

Regards,
Brett

topic Re: Field not found error even though it exists. in QlikView

Field not found error even though it exists.

Re: Field not found error even though it exists.

Re: Field not found error even though it exists.

Re: Field not found error even though it exists.