topic Max value for each common key in QlikView

Max value for each common key

Black_Hole — Sat, 16 Nov 2024 03:15:03 GMT

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.

Re: Max value for each common key

sunny_talwar — Wed, 19 Jun 2019 11:57:50 GMT

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;

Re: Max value for each common key

Black_Hole — Tue, 25 Jun 2019 14:44:20 GMT

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.

Re: Max value for each common key

sunny_talwar — Tue, 25 Jun 2019 15:24:27 GMT

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

Re: Max value for each common key

Black_Hole — Tue, 25 Jun 2019 16:31:39 GMT

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