Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

juhoheino
New Contributor III

Trouble with lookup and concatenation

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Trouble with lookup and concatenation

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.

8 Replies
MVP
MVP

Re: Trouble with lookup and concatenation

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
Contributor III

Re: Trouble with lookup and concatenation

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

juhoheino
New Contributor III

Re: Re: Trouble with lookup and concatenation

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!

juhoheino
New Contributor III

Re: Re: Trouble with lookup and concatenation

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.

MVP
MVP

Re: Trouble with lookup and concatenation

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.

juhoheino
New Contributor III

Re: Re: Trouble with lookup and concatenation

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

MVP
MVP

Re: Trouble with lookup and concatenation

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

juhoheino
New Contributor III

Re: Trouble with lookup and concatenation

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