8 Replies Latest reply: Aug 26, 2013 3:22 AM by Heino Juho RSS

    Trouble with lookup and concatenation

    Heino Juho

      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