Trouble with lookup and concatenation

[Anonymous]

Hi all,

I have tables SlsOrder and SlsLine (linked via OrderNum) and I am trying to change the Price-field for each item in SlsLine; this is done by dividing it by field ExcRate (located in SlsOrder table, if currency is EUR it is 1). I do not want to join these two tables, just recalculate the Price-field.

Lookup-function seems like a good choice (both SlsOrder and SlsLine have already been loaded before the lines of code shown) for getting the ExcRate for the calculation. What I am having trouble with is extra rows forming in the SlsLine qvd; they contain only the OrderNum (if order nums for the 10 first records are 1 to 10 then these are the numbers shown in the extra rows).

Temp_SlsLine:
Concatenate LOAD
OrderNum,
(Price / (lookup('ExcRate','OrderNum',OrderNum,'SlsOrder'))) as Price
Resident SlsLine;

Am I missing something here? Is lookup to a different table inside resident load even possible?
Any help appreciated!

-Juho

UPDATE: I modified the script a bit and noticed that the lookup is not working - no value for ExcRate is saved into the qvd.
                Any ideas why so?

Message was edited by: Juho Heino

[tresesco]

Hello Juho,

ExcRate or Price? If no value is there for ExcRate then lookup would return null, right? If you meant Price then, that's a question. By any chance, is it possible that you share a sample app?

[michael_maeuser]

lookup to another table is not possible. i would do a mapping load of the field "ExcRate" into the table SLSline.

[Anonymous]

ExcRate, not Price; I wrote the update unclearly but I basically tested only

(lookup('ExcRate','OrderNum',OrderNum,'SlsOrder')) as ExcRate

in the backend (load from source database) without doing the calculation Price/ExcRate as Price.

I do not think that sharing a sample app would help, because I have the following line in my front end and it works (loads data from SlsLine.qvd);

(Price / (lookup('ExcRate','OrderNum',OrderNum,'SlsOrder'))) as NewPrice,

I guess it has something to do with load orders and such, but SlsOrder has been loaded prior to this line.

Please see  the attached qvs!

[Anonymous]

Lookup to another table is possible when the source is qvd - the same line I try to insert to my backend works in my frontend where data is loaded from qvd...

If lookup does not work when loading from database then I will have to try your approach with mapping load.

[tresesco · Accepted solution]

You are right, load order matters here, specially when you use join and such complex operation the order of load is not well defined, and lookup returned value depends on the order when you have multiple values in the target field against a looked up value.

[Anonymous]

So mapping load is the method of choice instead of lookup, as Michael suggested?

[tresesco]

Yes, if not join, go ahead with mapping load.

[Anonymous]

Thank you both tresesco and Michael, I solved the issue by using Mapping Load and piped load (multiple load statements 'on top of each other'). My solution can be seen from the attached qvs should someone else face the same problem!

-Juho