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

    lookup in load from qvd return no value

    Louwrie Terblanche

      Hi,

       

      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

       

      dddddd.png

       

      eeee.png

       

      Thank so much

       

      Rgrds

      Louw

        • Re: lookup in load from qvd return no value

          Hi

           

          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

          CAL_MAP:

          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.

           

          Nigel.