Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
See my answer to another recent post, here:
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);
Try this :
=Aggr(nodistinct only( if (Size = Aggr(nodistinct max(Size), Prod), Unit)), Prod)
Hope this helps.
Regards,
Aditya
thank you Aditya, @Aditya_Chitale
they all return null...
i was inspired by one of the threads, and i came up with the following, but get an error:
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!
@alexpanjhc can you share sample data and the expression you are using in your variable ?
Regards,
Aditya
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