topic Re: How can I replace empty or wrong field values with correct ones? in QlikView

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

schumi1980 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Wed, 28 Dec 2016 12:35:09 GMT

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?

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

engishfaque — Wed, 28 Dec 2016 15:15:17 GMT

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