topic Re: Distinct Applymap in QlikView

Distinct Applymap

Anonymous — Wed, 21 Jun 2017 07:54:44 GMT

Dear All,

I got a mapping Table with 'Object Number' and 'Order Income':

Map_OrderIncome:

mapping load Distinct

Object.Number,

Order.Income

Resident

CE1;

this should be mapped to the Facttable

Fact:

load

ApplyMap('Map_OrderIncome',Object.Number,'') as Order.Income //

Resident

Fact_temp;

The result is as follows:

Sales Number Object Number (i.e.) Document Number Order Income

1234 1234_1 123456789 1.000

1234 1234_2 123456788 1.000

...

But the result should be

Sales Number Object Number (i.e.) Document Number Order Income

1234 1234_1 123456789 1.000

1234 1234_2 123456788 1.000

1234 1234_2 123456788 -

...

I want to have just one record per Object Number in my Table. Is this possible?

Thanks in advance

Lukas

Re: Distinct Applymap

Kushal_Chawda — Wed, 21 Jun 2017 07:59:12 GMT

How would you decide that to which record I need to do apply map? Please provide more information

Re: Distinct Applymap

Anonymous — Wed, 21 Jun 2017 08:04:44 GMT

Doesn't really matter to me. The first sorted value (Object Number) would be ok. It is just so I don't have multiple Order Income values if the same Object Number is recorded more than once.

Is that the information you neede?

Best regards

Re: Distinct Applymap

jaumecf23 — Wed, 21 Jun 2017 09:03:32 GMT

Hi,

You can do something like this when you load the table for the ApplyMap:

Map_OrderIncome:

mapping load Distinct

Object.Number,

Order.Income

Resident

CE1

WHERE SubField(Object Number,'_',2) = 1;

In this way you will only load the Object Mumber values like this *_1. Then in this way when in the applymap will try to search for a value different from *_1 a null will be returned.

Re: Distinct Applymap

avinashelite — Wed, 21 Jun 2017 09:25:05 GMT

Try this

Map_OrderIncome:

mapping load Distinct

Object.Number,

Order.Income

Resident

CE1;

this should be mapped to the Facttable

Fact:

LOAD *,

if(Sales Number=peek(Sales Number) and Object Number=peek(Object Number),'',Order Income) as New_Order_Income;

load

ApplyMap('Map_OrderIncome',Object.Number,'') as Order.Income //

Resident

Fact_temp;

Re: Distinct Applymap

Anonymous — Wed, 21 Jun 2017 10:18:52 GMT

Thanks that worked for me.

How do the peek function and the load statement within the load statement work in that context?

Best regards

Lukas

Re: Distinct Applymap

Anonymous — Wed, 21 Jun 2017 10:21:01 GMT

There are too many Object Numbers per Sales Number and they can change from time to time. But thank you!

Re: Distinct Applymap

avinashelite — Wed, 21 Jun 2017 10:32:31 GMT

Ha ha ok let me explain , actually we don't have any option in the applymap() function to handle this scenario so I took a different route .

1. peek function - will give the last loaded value of the field

2. Insted of creating resident load table I took Presiding load approach to reduce the script statement

if(Sales Number=peek(Sales Number) and Object Number=peek(Object Number),'',Order Income) as New_Order_Income;

Sales Number=peek(Sales Number) and Object Number=peek(Object Number)

here Sales Number and Object Number I comparing with the last loaded value and if both are same then I am forcefully making it as null i.e for the first occurrence it will give the value for rest it will give null ...which is your requirement

Hope this will help you to understand

Re: Distinct Applymap

Anonymous — Wed, 21 Jun 2017 10:40:08 GMT

Oh the preceding load actually would reduce my resident loads tremendously!

And I will keep in mind the peek function

Re: Distinct Applymap

avinashelite — Wed, 21 Jun 2017 10:43:34 GMT

make a note preceding load does not work with group by ......

Re: Distinct Applymap

Anonymous — Wed, 21 Jun 2017 10:46:14 GMT

Bold on a post-it! Thanks!