Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.