4 Replies Latest reply: Oct 12, 2012 4:44 PM by Henrik Matz RSS

    How tom mark max values only

    steve peroni

      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

       

        • Re: How tom mark max values only
          Henrik Matz

          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

            • Re: How tom mark max values only
              steve peroni

              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

                • Re: How tom mark max values only
                  Henrik Matz

                  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

              • Re: How tom mark max values only
                jagan mohan rao appala

                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.