Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
schumi1980
Contributor III
Contributor III

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

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

2 Replies
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
Specialist III
Specialist III

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