lookup in load from qvd return no value

[louwriet]

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

Thank so much

Rgrds

Louw

[#2]

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.