Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
swethasmilek
New Contributor III

Apply map to exlude one column values

Hi friends,facing issue on apply map having 5 columns where to get measure Recurring Revenue  need to combine 3 columns and mapping with apply map to get result problem is while combine 3 columns one column say revenue I need to exclude '00' need to have rest of columns,for same column revenue need to have '00'exclude rest of combinations as it is to get

Discount revenue measure

Tags (2)
9 Replies
vardhancse
Valued Contributor III

Re: Apply map to exlude one column values

for combining 3 columns use resident table first.

ABC:

LOAD

A,

B,

C

From Table_1;

NoConcatenate:

Fact:

LOAD *,

(A+B+C) as D,

Resident ABC;

Drop Table ABC;

Then try to apply applymap using mapping table

chinnakatikisg
Valued Contributor

Re: Apply map to exlude one column values

Hey,

Attach Sample file  or explain with sample data..

Regards,

Chinna

Not applicable

Re: Apply map to exlude one column values

hey,

try using the where caluse when combining the 3 columns,like

where revenue<>00

cheers,

kunal bhattacharjee

sujeetsingh
Honored Contributor III

Re: Apply map to exlude one column values

You van use exist function in resident load.

Partner
Partner

Re: Apply map to exlude one column values

Hi,

Try if statement

IF(Field1='00',Field1,ApplyMap('MAP_Name',Field1&Field2&Field3)) AS NewField...

Regards,
Sergey
swethasmilek
New Contributor III

Re: Apply map to exlude one column values

Hi Attaching Data and QVW can any one do needful to solve issue How to exclude one column particular values like measure Discount Revenue Currency Sum are not getting Desired ones to Exclude RI 00 ?How to write condition Expression in MTSales which should have all measures

Partner
Partner

Re: Re: Apply map to exlude one column values

Hi,

There are some issues with application... but, I probably sorted it out.

1. You have to add Num(Field, '00') to see all the numbers in appropriate format

So. now your MT Sales is

  ApplyMap('RR_FLAG_SALE_AL_TT_RI', Num(Account,'00') &'-' & Num(Transaction,'00')&'-'&  Num([Report Issue],'00'),
  ApplyMap('DR_FLAG_SALE_AL_TT_MGR_RI',Num(Account,'00') &'-' &Num(Transaction,'00')&'-'& Num(Manager,'00') &'-'&Num([Report Issue],'00'),'Others')) as  MTSales


2. You don't have appropriate records in Mapping Load. So I added only one additional to check that my assumptions are correct

33-76-89,Recurring Revenue

PFA

Regards,
Sergey
swethasmilek
New Contributor III

Re: Re: Apply map to exlude one column values

Thanx for Your Response,In real data all matching values are present,For Recurring Revenue i'm getting Desired Results where RI -00,where as in Discount Revenue Not getting Desired Output let say 2045434 Desired O/P getting sum as 35400,there is missing of mapping records not exactly exclude RI -00 take rest of Combinations?

Is there any possiblities to code condition in Apply may to Exclude 00 take remaining

Partner
Partner

Re: Re: Apply map to exlude one column values

Hi Swetha,

Could you, please, use more "." symbols in your messages? It could help you, because more people will manage to understand you.

And full names of the fields: For example What is RI to O/P- I can't find it.

Probably RI - is Report Issue...

So I spent more time to understand what you wrote, then doing something in QlikVIew with your data.

Regards,

Sergey

P.S.:

As an answer to you last question I can provide to use IF. Something like

IF(([Report Issue]=0, Null(),

  ApplyMap('RR_FLAG_SALE_AL_TT_RI', Num(Account,'00') &'-' & Num(Transaction,'00')&'-'&  Num([Report Issue],'00'),
   ApplyMap('DR_FLAG_SALE_AL_TT_MGR_RI',Num(Account,'00') &'-' &Num(Transaction,'00')&'-'& Num(Manager,'00') &'-'&Num([Report Issue],'00'),'Others'))) as  MTSales

In this case you will apply ApplyMap only, where Report Issue <>0 or '00'. It doesn't matter in your case.

Regards,
Sergey