Peek Function in script - error

[#1]

Hi all,

Please can you kindly  assist me with the script below:

I have 2 tables:  (1.) OrignalTestLookup and (2.) OrignalAdditionalDetails.

I am trying to include two columns from table 1 (OrignalTestLookup) in table 2 (OrignalAdditionalDetails). These coulmns are:

[Mapped Office Code]

[Mapped Test Staff]

I have used the Peek Function but I am not getting the right result.

...............................................................................................................................

Map_TestLookUpCode:

  MAPPING LOAD
Office,
Office

FROM
[..\Data\ABC Test Staff Lookup.xls]
(biff, embedded labels, table is Sheet1$);

Map_TestLookUpStaff:
MAPPING LOAD
Staff,
Staff

FROM
[..\Data\ABC Test Staff Lookup.xls]
(biff, embedded labels, table is Sheet1$);

OrignalTestLookup:
LOAD Office as [Office Code],
Office as [TestLookup Office Code],
      Staff AS [TestLookup Test Staff],
     Office as [BM Office],
     [Main Office] as [BM Office Main Office],
     Area as [BM Area],
     Region as [BM Region],
     Type as [BM Type],
     [Branch Director],
     [Regional Director],
     [Core / Non Core],
     [NN specialism]

FROM
[..\Data\ABC Test Staff Lookup.xls]
(biff, embedded labels, table is Sheet1$);


TestLookup:
LOAD*,
ApplyMap('Map_TestLookUpCode',[TestLookup Office Code], '') AS [Mapped Office Code],
ApplyMap('Map_TestLookUpStaff',[TestLookup Test Staff], '') AS [Mapped Test Staff]
Resident OrignalTestLookup;
DROP TABLE OrignalTestLookup;

OrignalAdditionalDetails:
LOAD ABC_client_key,
    MaxString([Office Code]) AS [Office Code],
    MaxString([Office Code]) AS [AdditionalDetails Office Code],
    MaxString([Update Desc]) as [Update Desc],
    MaxString([BIS/CIN No]) as [BIS/CIN No],
    MaxString([CIN]) as [CIN],
    MaxString([Test Staff]) as [AdditionalDetails Test Staff],
    MaxString([Relationship Staff]) as [Relationship Staff],
    MaxString([Introducing Q6 Code]) as [Introducing Q6 Code],
    date(max([Next Action Date]),'DD-MM-YYYY') as [Next Action Date],
    DATE(max([Set Up Date]),'DD-MM-YYYY') as [Set Up Date],
    DATE(max([Client Called Date]),'DD-MM-YYYY') as [Client Called Date],
    DATE(max( [BM Called Date]),'DD-MM-YYYY') as [BM Called Date],
    DATE(max( [Last Logged in Date]),'DD-MM-YYYY') as [Last Logged in Date]
FROM
[..\Data\ClientDetails_Acturis.QVD]
(qvd)
GROUP BY ABC_client_key;

AdditionalDetails:
LOAD*,

IF([AdditionalDetails Office Code]= Peek('[Mapped Office Code]',0,'TestLookup'),Peek([Mapped Test Staff],0,'TestLookup'),
IF([AdditionalDetails Office Code]<> Peek('[Mapped Office Code]',0,'TestLookup'),'Default')) AS [Test Staff]
Resident OrignalAdditionalDetails;
DROP TABLE OrignalAdditionalDetails;

Regards.

[francoiscave]

Hi Phresh,

You need to use the ApplyMap function instead of the Peek function.

François

[#3]

Hi Francois,

I initially used ApplyMap instead of Peek but it does not seem to work...

Regards

[francoiscave]

Hi Phresh,

If you use the Mapping Load, you must use the ApplyMap function.

ApplyMap('mapname', expr [ , defaultexpr ] )

ApplyMap('Map_TestLookUpCode',expr,'Default')

François

[#5]

Hi Francois,

I get the error message below:

Field not found - <TestLookup Office Code>
AdditionalDetails:

IF([AdditionalDetails Office Code] =   ApplyMap('Map_TestLookUpCode',[TestLookup Office Code], '')'Pass',
IF([AdditionalDetails Office Code] <>   ApplyMap('Map_TestLookUpCode',[TestLookup Office Code], ''), 'Fail')) AS [Test Staff]