Replace

[Former Member]

Hi,

I have a specific requirement.

from the above picture,we can see for SalesDocument 0305980011 have different product lines,priority and primary products.

from the priority,we can see 30 is least priority.

now I have to replace the primaryproduct values of other priority lines in align with least priority value

(i.e change the primaryproduct values(INFORMATICS,CONSUM,SERVICES,REPAIRPARTS of priority lines:58,61,70 and 71 as LC)

Has any one come across such requirement,please share your inputs.

Thank you in advance.

Best Regards

ERP

[former_member237684]

Hi,

Not sure if you need to update the table or need a select query or need to do this in a view.

Please check if this would help.

WITH salest as (SELECT '0305980011' as SalesDocument ,'29' as productline,30 as priority,'LC' primaryproduct FROM DUMMY
       UNION ALL
        SELECT '0305980011' as SalesDocument ,'LI' as productline,58 as priority,'Info' primaryproduct FROM DUMMY
    UNION ALL
        SELECT '0305980011' as SalesDocument ,'AA' as productline,61 as priority,'Consum' primaryproduct FROM DUMMY)


SELECT
    t.SalesDocument, t.productline, t.priority, t2.primaryproduct
FROM salest t
INNER JOIN (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY SalesDocument ORDER BY priority) AS rn
                                             FROM salest) t2
              ON t.SalesDocument = t2.SalesDocument
WHERE t2.rn = 1

Or

UPDATE YourTable t
    SET primaryproduct = (
                                                     SELECT primaryproduct
                                                     FROM YourTable t1
                                                     WHERE 
                                                              t1.SalesDocument = t.SalesDocument
                                                         AND t1.priority = (SELECT MIN(t2.priority) FROM YourTable t2 WHERE t2.SalesDocument = t.SalesDocument)
                                            )

Thanks
Mani

[Former Member]

Hi Mani,

Thank you for quick reply.

I am looking to implement this logic in Graphical Calculation view and not in sql calc.view.

Kindly advise on the ways to acheive this.

Thank you in advance.

Best Regards

ERP

[former_member237684]

Hi,

If you are in SPS09 and above you can do the same as I have done in my first SQL query by using RANK node in Graphical calculation view.

Thanks

Mani

[Former Member]

Hi Mani,

Please elaborate with above example in graphical cv.

Thank you in advance

Best Regards

ERP

[former_member237684]

Hi ,

I mean something like this would help.

Thanks

Mani