show the values based on the max vlaue

[alexpanjhc]

Hi

i have the following table now.

Prod	Category	Size	Unit	Price
A	Furniture	10	2	2
A	Electronics	20	3	2.5
A	Office	30	4	2

now i am asked to show the unit and price by the biggest size of A

so in this case unit and price will be 4 and 2 for all three rows.

how do i make this happen in set analysis?

thanks!

[daturpin]

See my answer to another recent post, here:

https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-get-previous-rows-last-column-values-in-curre...

I don't have time to write the sample code, but this problem is similar in that you are doing a line-by-line, on the fly transformation of data. This one is going to require a nested FOR loop, though, because you need to go through all "A" values to find the max Size, then go through a second time to pick up the other values you don't want to change, write those and the values associated with the max Size to a new table using an ADD LOAD, then go back and do it for B, C, D, and etc.

To start with, you want to use AutoNumber to give you a numerical value to iterate.

So:

[temp_table]

REPLACE LOAD

Prod,

Category,

Size,

Unit,

Price,

Autonumber(Prod) as Iterator

from $(your_path)/your_file.qvd (qvd);

[Aditya_Chitale]

@alexpanjhc 

Try this :

=Aggr(nodistinct only( if (Size = Aggr(nodistinct max(Size), Prod), Unit)), Prod)

Hope this helps.

Regards,

Aditya

[alexpanjhc]

thank you Aditya, @Aditya_Chitale 

they all return null...

[alexpanjhc]

i was inspired by one of the threads, and i came up with the following, but get an error:

Error in set modifier ad hoc element list: ',' or ')' expected

only({<[Cat]={"=Rank(max(Aggr($(vCoalitionView_ Size) , [Cat]))) =1"}>}Price)

I tested that this is correct without error   :max(Aggr($(vCoalitionView_ Size) , [Cat])) 

please help!

[Aditya_Chitale]

@alexpanjhc can you share sample data and the expression you are using in your variable ?

Regards,

Aditya

[alexpanjhc]

thanks for replying @Aditya_Chitale 

the data model is very complex but i can share the expression

sum(DISTINCT{<[-TimeKey]={"$(=$(=vWalletMaxYear))"}>} Wallet)

vWalletMaxYear=2022/12/30