How can I replace empty or wrong field values with correct ones?

[schumi1980]

Hi All,

I am uploading the following file:

PolicySummary:

LOAD PolicyNumber,

     ProgramNbr,

     PolicyCcy,

     ApplyMap('ExchangeRates',PolicyCcy) as Policy_FX,

     PolicyPremiumOrig

FROM

[\\Property DB\Full\PolicySummary.xlsx]

(ooxml, embedded labels, table is Sheet1);

However, certain 'PolicyNumber' do not have a  'ProgramNbr'.

I was looking for suggestions in the Community, but I could not find any solution (or as a non-native English speaker, I was not able to).

I tried:

MissingQuotes:

Mapping LOAD PolicyNumber,

     ProgramNbr

FROM

[\\MissingQuotes_2016-12-27.xlsx]

(ooxml, embedded labels, table is Sheet1);

PolicySummary:

LOAD PolicyNumber,

     if(IsNull(ProgramNbr), ApplyMap('MissingQuotes',PolicyNumber),ProgramNbr),

     PolicyCcy,

     ApplyMap('ExchangeRates',PolicyCcy) as Policy_FX,

     PolicyPremiumOrig

FROM

[\\Property DB\Full\PolicySummary.xlsx]

(ooxml, embedded labels, table is Sheet1);

But it did not work.

Could somebody point me in the correct direction?

Many thanks.

Schumi1980

[sunny_talwar]

I don't see any reason why the code should not work... Would you be able to share some sample data to test it out?

[engishfaque]

Dear Jan,

Kindly check given below script,

MissingQuotes:

Mapping

LOAD Distinct PolicyNumber,

     ProgramNbr

FROM

[\\MissingQuotes_2016-12-27.xlsx]

(ooxml, embedded labels, table is Sheet1);

PolicySummary:

LOAD PolicyNumber,

     if(Len(Trim(ProgramNbr)) = 0, ApplyMap('MissingQuotes',PolicyNumber, 'Not Available'), ProgramNbr) as ProgramNbr,

     PolicyCcy,

     ApplyMap('ExchangeRates',PolicyCcy, '#NA') as Policy_FX,

     PolicyPremiumOrig

FROM

[\\Property DB\Full\PolicySummary.xlsx]

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed