Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Black_Hole
Creator II
Creator II

Max value for each common key

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_INVCODE_SYSTCODE_SYST_CORR
1021FALSE
1141FALSE
1021111
1141111
1021111
1141111

 

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.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

4 Replies
sunny_talwar

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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! 👍