Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
How would you decide that to which record I need to do apply map? Please provide more information
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
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.
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;
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
There are too many Object Numbers per Sales Number and they can change from time to time. But thank you!
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
Oh the preceding load actually would reduce my resident loads tremendously!
And I will keep in mind the peek function
make a note preceding load does not work with group by ......