Max value for each common key

[Black_Hole]

Hello all,

I don't know how I can fill a column C with the max value of the column B grouped by key (column A).

     COL   A                COL   B                COL   C

%_KEY_INV	CODE_SYST	CODE_SYST_CORR
102	1	FALSE
114	1	FALSE
102	11	11
114	11	11
102	11	11
114	11	11

I would like to be able to do that:

If CODE_SYST_CORR = FALSE Then CODE_SYST_CORR= max(CODE_SYST) grouped by Key_INV

For ex. CODE_SYST_CORR= 11 in the 1st row .

Please could tell me if it's possible to do that in a script? 

I hope this is clear...

Thank you in advance for your help.

[sunny_talwar · Accepted solution]

Try something like this

Table:
LOAD * INLINE [
    %_KEY_INV, CODE_SYST, CODE_SYST_CORR
    102, 1, FALSE
    114, 1, FALSE
    102, 11, 11
    114, 11, 11
    102, 11, 11
    114, 11, 11
];

Left Join (Table)
LOAD %_KEY_INV,
	 Max(CODE_SYST) as Max_CODE_SYST
Resident Table
Group By %_KEY_INV;

FinalTable:
LOAD RowNo() as RowNum,
	 %_KEY_INV,
	 CODE_SYST,
	 If(CODE_SYST_CORR = 'FALSE', Max_CODE_SYST, CODE_SYST_CORR) as CODE_SYST_CORR
Resident Table;

DROP Table Table;

[Black_Hole]

Hello @sunny_talwar ,

Thank you very much for your reply.

The script works, but I meet a big problem...

Using the function Group By, some records are not displayed in my table. 

So, I don't think to make it right using the function Group By. 

Please could you advise me what function is more suited in my case.

Thank you in advance for your help.

[sunny_talwar]

Can you share a sample where we can see the issue?

[Black_Hole]

Hello @sunny_talwar ,

Finally, I resolved the problem in adding another key (RowNum() as ID) in my main table.

In this way, your previous solution gives me the expected result.

Thank you very much for your precious help! 👍