Add a column in a table from the script

[#1]

Hello,

I have 2 tables which i read from SAP: EKKO and EKPO.

EKKO contains the headers of the orders and EKPO contains all the lines in the orders.

Here is the thing, i want to add a column in EKKO depending on the value of a column in EKPO.

There is my EKKO table:

Order	Date
1	18.11.2015
2	19.11.2015
3	20.11.2015

There is my EKPO table:

Order	Status	Quantity
1	X	1
1		2
2	X	3
2	X	4
2	X	5
3	X	6

And there is the EKKO table i'm looking for:

Order	Date	Status
1	18.11.2015
2	19.11.2015	X
3	20.11.2015	X

For the same Order,

    If Status = 'X' for each EKPO line

          Add a column Status in EKKO with the value 'X'

How can i do this please?

This is how i loop the EKPO table but i have no idea how to do the rest...

LET vNumRows=NoOfRows('EKPO');

FOR i=1 to $(vNumRows)

  LET vStatus=Peek('Status',$(i));

    //do the stuff

NEXT;

Thank you very much for your responses,

Best Regards,

Loïc

[Gysbert_Wassenaar]

Maybe like this:

EKKO:

LOAD * FROM EKKO;

EKPO:

LOAD * FROM EXPO;

JOIN(EKKO)

LOAD Order, FirstSortedValue(Status, -Quantity) as Status

Resident EKPO

GROUP BY Order;

[#3]

This could work for my little example but not for my whole table.

This is not what i want, you just sort EKPO by the Quantity and take the Status of the bigger to put in EKKO.

I have to watch the Status of every lines in EKPO and only if all the lines have Status 'X' for an Order, put 'X' in the column Status from EKKO.

[Gysbert_Wassenaar]

EKKO:

LOAD * FROM EKKO;

EKPO:

LOAD

     Order,

     If(len(trim(Status))=0;'N/A',Status) as Status

FROM EXPO;

JOIN(EKPO)

LOAD Order, Count(distinct Status) as StatusCount

Resident EKPO

GROUP BY ORder;

JOIN(EKKO)

LOAD distinct Order, Status

Resident EKPO

WHERE StatusCount = 1 and Status = 'X';

[#5]

JOIN(EKPO)

LOAD Order, Count(distinct Status) as StatusCount

JOIN(EKKO)

LOAD distinct Order, Status

Resident EKPO

WHERE StatusCount = 1 and Status = 'X';

I think you missed something there.

[Gysbert_Wassenaar]

Yeah, you're right. Fixed now.

[#7]

Hmm yeah that's what i thought...

I got this error message:

"Invalid expression

JOIN(EKPO)

LOAD Order, Count(distinct Status) as StatusCount Resident EKPO"

I think you can't JOIN a table and LOAD from this same table.

[Gysbert_Wassenaar · Accepted solution]

See attached example.