UNAABLE TO USE LOOKUP

[#1]

Hi

please help me with this code

FBP_Temp:

LOAD Therapy,
[Management Entity ID],
[Management Entity Name],
[Material Name],
[Material ID],
[Pack Size],
[Content Unit],
Content,
[Strength Description],
[Galenic Form],
[Numerator (Unit 1)],
[Denominator (Unit 2)],
[Country Key],
[Product ID],
[Product Name],
[Group of Products],
[Product Name 2],
[Fiscal year],
[Posting period],
[Cumulative Value LC],
[Cumulative Value GC],
[Cumulative Quantity],
Therapy&

[Material ID]&

[Country Key]&

[Fiscal year]as key

FROM

(ooxml, embedded labels, table is FBP);

qualify*;
UNQUALIFY key;
FBP:
NoConcatenate
Load*,

if(key=Previous(key),[Cumulative Value LC]-Previous([Cumulative Value LC]),[Cumulative Value LC]) as [MONTHLY VALUE LC],
if(key=Previous(key),[Cumulative Value GC]-Previous([Cumulative Value GC]),[Cumulative Value GC]) as [MONTHLY VALUE GC],
if(key=Previous(key),[Cumulative Quantity]-Previous([Cumulative Quantity]),[Cumulative Quantity]) as [MONTHLY VOLUME]
Resident FBP_Temp

order by

key,
[Posting period];

Drop Table FBP_Temp;
UNQUALIFY*;


LU_Temp:
LOAD Therapy,
[Management Entity ID],
[Management Entity Name],
[Material Name],
[Material ID],
[Pack Size],
[Content Unit],
Content,
[Strength Description],
[Galenic Form],
[Numerator (Unit 1)],
[Denominator (Unit 2)],
[Country Key],
[Product ID],
[Product Name],
[Group of Products],
[Product Name 2],
[Fiscal year],
[Posting period],
[Cumulative Value LC],
[Cumulative Value GC],
[Cumulative Quantity],
Therapy&

[Material ID]&

[Country Key]&

[Fiscal year]as key

FROM

(ooxml, embedded labels, table is LU);

qualify*;
UNQUALIFY key;
LU:
NoConcatenate
Load*,

if(key=Previous(key),[Cumulative Value LC]-Previous([Cumulative Value LC]),[Cumulative Value LC]) as [MONTHLY VALUE LC],
if(key=Previous(key),[Cumulative Value GC]-Previous([Cumulative Value GC]),[Cumulative Value GC]) as [MONTHLY VALUE GC],
if(key=Previous(key),[Cumulative Quantity]-Previous([Cumulative Quantity]),[Cumulative Quantity]) as [MONTHLY VOLUME]
Resident LU_Temp

order by

key,
[Posting period];

Drop Table LU_Temp;
UNQUALIFY*;



DATA_Temp:
LOAD Therapy,
[Management Entity ID],
[Management Entity Name],
[Material Name],
[Material ID],
[Pack Size],
[Content Unit],
Content,
[Strength Description],
[Galenic Form],
[Numerator (Unit 1)],
[Denominator (Unit 2)],
[Country Key],
[Product ID],
[Product Name],
[Group of Products],
[Product Name 2],
[Fiscal year],
[Posting period],
[Cumulative Value LC],
[Cumulative Value GC],
[Cumulative Quantity],
Therapy&
[Material ID]&
[Country Key]&
[Fiscal year]as key

FROM

(ooxml, embedded labels, table is DATA);

qualify*;
UNQUALIFY key;
DATA:
NoConcatenate
Load*,

if(key=Previous(key),[Cumulative Value LC]-Previous([Cumulative Value LC]),[Cumulative Value LC]) as [MONTHLY VALUE LC],
if(key=Previous(key),[Cumulative Value GC]-Previous([Cumulative Value GC]),[Cumulative Value GC]) as [MONTHLY VALUE GC],
if(key=Previous(key),[Cumulative Quantity]-Previous([Cumulative Quantity]),[Cumulative Quantity]) as [MONTHLY VOLUME],

lookup([MONTHLY VALUE GC],key,key,'LU') as LU_Mth_GC,
lookup([MONTHLY VALUE GC],key,key,'FBP') as FBP_Mth_GC
Resident DATA_Temp

order by
key,
[Posting period];

Drop Table DATA_Temp;
UNQUALIFY*;

It shows error field not found [Monthly value GC]

[marcus_sommer]

You have no [MONTHLY VALUE GC] within DATA_Temp from where you are loading. In this case you need to include the expression for these field within the lookup:

lookup(if(key=Previous(key),[Cumulative Value GC]-Previous([Cumulative Value GC]),[Cumulative Value GC]),key,key,'LU') as LU_Mth_GC,

- Marcus

[#3]

Hi Marcus,

Thanks for the reply but am sorry that is not working. Code is executing but it is not fetching any values from other tables.

[marcus_sommer]

Yes, my suggestions with replacing the fieldname with its sorce-expression wasn't correct. But you need to refer to the right fields which are [LU.MONTHLY VALUE GC] and [FBP.MONTHLY VALUE GC] because of your qualifying the fields. Try to change the lookup to:

lookup([LU.MONTHLY VALUE GC],key,key,'LU') as LU_Mth_GC,

- Marcus

[#5]

Hi Marcus,

It is still not working. cant fetch any values

[marcus_sommer]

I can't see any mistake. Therefore I would check the tables. Are the resident-sorting, the calculation with the previous row, the qualify and so on working and return the expected results. Further lookup returned only the value from first matching - this meant if your key isn't absolute unique on row-level you will get unpredictable/unwanted results.

- Marcus