Peek Function in script - error

Thu, 04 Dec 2014 16:09:31 GMT

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.

Re: Peek Function in script - error

francoiscave — Thu, 04 Dec 2014 16:13:32 GMT

Hi Phresh,

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

François

Re: Peek Function in script - error

Thu, 04 Dec 2014 16:18:56 GMT

Hi Francois,

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

Regards

Re: Re: Peek Function in script - error

francoiscave — Thu, 04 Dec 2014 16:28:32 GMT

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

Re: Peek Function in script - error

Thu, 04 Dec 2014 17:26:53 GMT

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]

topic Re: Peek Function in script - error in QlikView

Peek Function in script - error

Re: Peek Function in script - error

Re: Peek Function in script - error

Re: Re: Peek Function in script - error

Re: Peek Function in script - error