Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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.
Can you share a sample where we can see the issue?
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! 👍