Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

louwriet
Not applicable

lookup in load from qvd return no value

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

Tags (2)
1 Reply
Not applicable

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.