topic Re: Apply map to exlude one column values in QlikView

Apply map to exlude one column values

Anonymous — Mon, 10 Nov 2014 04:55:19 GMT

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

Re: Apply map to exlude one column values

vardhancse — Mon, 10 Nov 2014 05:08:15 GMT

for combining 3 columns use resident table first.

ABC:

LOAD

From Table_1;

NoConcatenate:

Fact:

LOAD *,

(A+B+C) as D,

Resident ABC;

Drop Table ABC;

Then try to apply applymap using mapping table

Re: Apply map to exlude one column values

Anonymous — Mon, 10 Nov 2014 05:10:12 GMT

Hey,

Attach Sample file or explain with sample data..

Regards,

Chinna

Re: Apply map to exlude one column values

Mon, 10 Nov 2014 05:17:46 GMT

hey,

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

where revenue<>00

cheers,

kunal bhattacharjee

Re: Apply map to exlude one column values

sujeetsingh — Mon, 10 Nov 2014 05:26:16 GMT

You van use exist function in resident load.

Re: Apply map to exlude one column values

SergeyMak — Mon, 10 Nov 2014 05:29:05 GMT

Hi,

Try if statement

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

Re: Apply map to exlude one column values

Anonymous — Mon, 10 Nov 2014 08:33:25 GMT

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

Re: Re: Apply map to exlude one column values

SergeyMak — Mon, 10 Nov 2014 08:52:58 GMT

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

Re: Re: Apply map to exlude one column values

Anonymous — Mon, 10 Nov 2014 10:06:55 GMT

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

Re: Re: Apply map to exlude one column values

SergeyMak — Tue, 11 Nov 2014 03:09:02 GMT

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(),

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