Distinct Applymap

[Anonymous]

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

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

[Kushal_Chawda]

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

[Anonymous]

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

[jaumecf23]

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.

[avinashelite · Accepted solution]

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;

[Anonymous]

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

[Anonymous]

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

[avinashelite]

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 

[Anonymous]

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

And I will keep in mind the peek function

[avinashelite]

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