Field not found error

[#1]

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;

[Peter_Cammaert]

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?

[Peter_Cammaert]

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

[Peter_Cammaert]

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.

[#5]

Yes, and multiplies by the amount field in MainLineItems

[#6]

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

[Peter_Cammaert · Accepted solution]

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.

[#8]

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!

[Peter_Cammaert]

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