topic Re: Field not found error in QlikView

Field not found error

Tue, 07 Jul 2015 12:01:54 GMT

Hello experts,

I am not quite shure what I am doing wrong here, I wonder if I am missing a JOIN. There is the slightly shortened codes (omiting fields that are unused). What I am trying to do is get the exchange rate from one table, if it is zero make it 1 or else keep it (this part works fine, I believe) then multiply the exchange rate by the amount (DMBTR)....that is where I get the fail "Table Not found....LOAD (DMBTR * ExchRate) As tempSpend_NOK
Resident MainLineItems;".

I have tried to also load it from the .qvd file also (non-resident) but still no luck. Any ideas on where I am going wrong?

LOAD (DMBTR * ExchRate) As tempSpend_NOK
Resident MainLineItems;

FixExRateZero:
Load If(UKURS = 0, 1, UKURS) As ExchRate;
//// If exchange rate = 0 set value to 1.

MainHeader:

SQL SELECT

    BELNR,
    ....
    UKURS

FROM TABLE_X ;

Store MainHeader Into C:\QlikView\dgMainHeader.qvd;

MainLineItems:

SQL SELECT

BELNR,
......
    DMBTR
FROM TABLE_Y;

Store MainLineItems Into C:\QlikView\dgMainLineItems.qvd;

Re: Field not found error

Peter_Cammaert — Tue, 07 Jul 2015 12:05:14 GMT

The LOAD RESIDENT MainLineItems; comes before the MainLineItems table is created (at the bottom of your script). There is a causality error in this logic, don't you think?

Re: Field not found error

Peter_Cammaert — Tue, 07 Jul 2015 12:07:23 GMT

I guess the second LOAD is a PRECEDING LOAD, but it keeps only one (new) field called ExchgRate. Is that your intent?

Re: Field not found error

Peter_Cammaert — Tue, 07 Jul 2015 12:09:25 GMT

Finally, UKURS (and by extension ExchgRate) and DMBTR belong to different tables. You cannot multiply them in a single LOAD without using applymap or a JOIN to put the two fields in the same table.

Re: Field not found error

Tue, 07 Jul 2015 12:09:34 GMT

Yes, and multiplies by the amount field in MainLineItems

Re: Field not found error

Tue, 07 Jul 2015 12:10:29 GMT

JOIN is what I thought the problem would be...

Re: Field not found error

Peter_Cammaert — Tue, 07 Jul 2015 12:11:35 GMT

Well, if you want to combine fields from different tables, you have to either JOIN them first or use applymap() or something similar to get them into the same table. Only then you can perform the multiply operation.

Re: Field not found error

Tue, 07 Jul 2015 12:14:04 GMT

I forgot about the UKURS (and by extension ExchgRate) connection....I am sure it will work when I make a derived unique key on the other table and JOIN them...Thanks!

Re: Field not found error

Peter_Cammaert — Tue, 07 Jul 2015 12:17:25 GMT

For example, like this:

TempMainLineItems:

SQL SELECT BELNR, DMBTR

FROM TABLE_Y;

LEFT JOIN(TempMainLineItems)

SQL SELECT BELNR, UKURS

FROM TABLE_X;

MainLineItems:

NOCONCATENATE

LOAD *, ExchangeRate*DMBTR AS tempSpend_NOK;

LOAD BELNR, DMBTR, UKURS, IF (UKURS=0, 1, UKURS) AS ExchangeRate

RESIDENT TempMainLineItems;

DROP Table TempMainLineItems;

Note that SAP (I guess that is what your are reading data from) has a lot of restrictions with respect to what you can do with SQL.

Best,

Peter