Need Assistance

[#1]

Hi All,

I am working on incidents data that is stored in Oracle db. I need to show a graph with number of incidents reported as per the

Regions(North, APAC, South Europe &  Americas). For doing this, I used a column in database showing the country names and

mapped it with regions thru an Excel Sheet. I have attached the Excel Sheet for reference. For some incidents, the country

name is blank in database. When it is blank, I need to use an other column in database called "Customer Company" and

update the regions accordingly. After loading the data from database, I should be able to fetch the records showing the

Region name as blank and use Customer Company to update the regions. I am not able to do this. Request your assistance please.

Thanks,

Arun

[vinieme12]

When Loading your Region Field add an IF() to check for nulls and if it null use applymap() to populate CustomerCompany

Don't join - use Applymap instead

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/MappingFunctions/Apply...‌

MApCustomerCompany:

MAPPING Load

CustomerCompany,

Actual2

FROM YourSource;

FACT

Load

CustomerCompany,

If(len(Trim(Region))=0, Applymap('MApCustomerCompany',CustomerCompany),Region) as Region,   // Note CustomerCompany is the field reference from FACT table, if in FACT CustomerCompany was say CCompany then the condition would have been as follows

If(len(Trim(Region))=0, Applymap('MApCustomerCompany',CCompany),Region) as Region, 

[#3]

Hi Vineeth,

Thanks for your reply. I tried working on this, but still it did not get mapped and the Regions are blank.

Thanks,

Arun

[vinieme12]

please share sample data that replicates your scenario as the excel you attached has no null values to be mapped.

Can you share sample app or data that closely replicates thae data sets which you are working with

Preparing examples for Upload - Reduction and Data Scrambling

[#5]

Hi Vineeth,

Attached the sample data. In this sheet column A is Region and Column B is Customer Company and Column C is Country. I use Country to map Regions. But If you filter, you can see that Region is blank for some rows because Country is not updated. So when Region is blank, I should be able to use Column B i. e customer company and map the regions as per the Regions Excel that I attached.

Thanks,

Arun.

[tyagishaila]

HI Arun,

Try it, It is working fine for me-

TAB1:

Mapping

LOAD custcomp,

     Actual2

FROM

(ooxml, embedded labels, table is [As Per Customer Company]);

TAB2:

LOAD Region,

     [Customer Company],

     Country ,

    If(IsNull(Region), ApplyMap('TAB1',[Customer Company],'No Region'),Region) AS RegionNew

FROM

(ooxml, embedded labels, table is TB01_20170111_142716);