topic Need Assistance in QlikView

Need Assistance

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need Assistance

vinieme12 — Wed, 11 Jan 2017 11:10:43 GMT

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/ApplyMap.htm‌

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,

Re: Need Assistance

Thu, 12 Jan 2017 08:58:37 GMT

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

Re: Need Assistance

vinieme12 — Thu, 12 Jan 2017 09:05:35 GMT

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

Re: Need Assistance

Thu, 12 Jan 2017 09:22:49 GMT

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.

Re: Need Assistance

tyagishaila — Thu, 12 Jan 2017 10:48:26 GMT

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);