1 Reply Latest reply: Oct 23, 2012 11:50 AM by Nigel West RSS

    lookup in load from qvd return no value

    Louwrie Terblanche



      I would really appreciate help with this issue.


      I am loading information from a QVD and in the load statement i use a lookup function to pull a field out of a custom calendar that i created in excel.


      The senario is that the client has financial periods that are not calendar period for instance 31/01/2012 will normally be in the year month of 201201 but in this financial customer calendar i added an extra field and created a new year month field where this date will fall under 201202.


      This is the field i want to pull into my fact table(QVD) the dates are matching but does not pull any value through for this custom year month when i use datekey and invoicedate. I tried to use the datekey that is the actual link to the calendar table but it gives me an error when i do a rebuild "field not found <datekey> so presume that you cant use the "as fieldname" . That is why i use the invoice date instead with no luck, it rebuilds but with no returning data in the san_nummonthyear field. For information my calendar table is the first table that build then my transaction table - fact






      Thank so much




        • Re: lookup in load from qvd return no value



          I've actually never used the lookup function, but in the case your talking about I'd probably use an ApplyMap Function.


          1. Create a Map table


          MAPPING LOAD Date([DateTran]) as DateKey, San_Mth_Yr FROM U:\...... (your excel file);


          2. In your main table load statement replace the lookup function with

          ApplyMap('CAL_MAP',invoiceDate,0) AS dd



          That should do it.