Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I'm newer and I'm facing a problem too big for my experience. I've this scenario
Table1
Customer Article Line Value
A 1 1 100
A 1 2 200
A 1 3 80
B 2 1 40
B 2 2 30
B 2 3 10
C 1 1 20
C 1 2 100
I would insert a flag into the lines which have the maximum value for each combination of Customer,Article, line:
NewTable1
Customer Article Line Value FlagMax
A 1 1 100
A 1 2 200 *
A 1 3 80
B 2 1 40 *
B 2 2 30
B 2 3 10
C 1 1 20
C 1 2 100 *
Is it possible ?
Help please, I'm really lost
Thanks in advance
HI,
i would first creat a mapping table with customer and max(value) grouped by customer.
In the load of the table I would then use applymap function to lookup the max value. If the max value is equal to the value field I would set a flag.
it could be like this:
MAPmax:
mapping load
customer,
max(value)
from yourtable
group by customer
FInalTable:
load
*,
If(applymap('MAPmax',customer)=value,1) as FlagMax
from yourtable;
/henrik
HI,
i would first creat a mapping table with customer and max(value) grouped by customer.
In the load of the table I would then use applymap function to lookup the max value. If the max value is equal to the value field I would set a flag.
it could be like this:
MAPmax:
mapping load
customer,
max(value)
from yourtable
group by customer
FInalTable:
load
*,
If(applymap('MAPmax',customer)=value,1) as FlagMax
from yourtable;
/henrik
Hi,
Try this script below
Temp:
LOAD * INLINE [
Customer, Article, Line, Value
A , 1, 1 , 100
A, 1 , 2 , 200
A, 1 , 3 , 80
B , 2 , 1, 40
B, 2 , 2, 30
B , 2 , 3 , 10
C, 1 , 1, 20
C , 1 , 2 , 100
];
Data:
LOAD
*,
IF(Previous(Customer) <> Customer, '*') AS Flag
Resident Temp
ORDER BY Customer, Value Desc;
DROP TABLE Temp;
Hope this helps you.
Regards,
Jagan.
Thanks Henrik, if works but I didn't realized how.
Could you spent a bit of your time to explain how the mapping load works ?
Thanks in advance
stefan
A mapping load is used to create a mapping table.
A mapping table is a table of always 2 fields and the table only exists during the reload procedure. It is used when you want to lookup a value.
It's used a lot when you load a fact table and want to lookup a surgate dimension key from a dimension table. In this case you create a mapping table with the unique business key and the corresponding surrogate key.
Mapping load is faster than the lookup function, but the lookup function is more flexible.
You should take a look in the help area, and search for mapping load and lookup. I believe there are some examples.
/Henrik