Dynamic Sort value using IF

[#1]

Hi All,

I have one requirement I have two fields Region and Type.

Type contains different types like Diamond, Gold, Platinum, Silver etc..

As per my requirement I need to sort Type dynamically as below

Default sort order for Type is 1.Diamond, 2.Gold, 3.Platinum, 4.Silver

But  in AMS region we don't have Gold type here sort should be like 1.Diamond, 2.Platinum, 3.Silver

In Europe region we don't have Platinum type here sort should be like 1.Diamond, 2.Gold, 3.Silver

My intension is first it should check the type if the value exists then assign number accordinglly.

If value is not exists then sort value should assign to next value as per order.

I want to display my values like Dimond-1,Gold-2,Platinum-3,Silver-4

Can anybody help me here to assign sort value dynaically using if condition or any other ideas?

Thanks,

QV11 User

[swuehl]

That's the script, second table added by me:

Table1:
LOAD * Inline [
Region,Type
Asia,Gold
Asia,Diamond
Asia,Platinum
Asia,Silver
America,Diamond
America,Platinum
America,Silver
Europe,Gold
Europe,Diamond
Europe,Silver
];


LOAD * INLINE [
Type, Order
Diamond, 1
Gold, 2,
Platinum,3
Silver,4
];

If you select a Region, different sets of Type are possible, the OP want to add a custom order number 1.,2.,3. to each Type value.

You can achieve this using this expression

=Concat( Aggr( Rank(-Order) & '. ' &Type, Type) , ';', Order)

[#3]

Hi Swuehl,

Can you extend your valuable help please?

I have data like as below

Table1: 

1. LOAD * Inline [ 
2. Region,Type,SubREgion
3. Asia,Gold, India
4. Asia,Diamond,India
5. Asia,Platinum,China
6. Asia,Silver,India
7. America,Diamond,Washington
8. America,Platinum,Canada
9. America,Silver,Washington
10. Europe,Gold,Ireland
11. Europe,Diamond,France
12. Europe,Silver,Ireland
13. ]; 

In the previous solution it's working at Region level but If I create Table with SubRegion 'India' then the order should be Diamond-1,Gold-2,Silver-3. For SubRegion Washington table order should be Diamond-1,Silver-2.

Is there any way If i select Region then Subregion values also dynamically sort based on available Types in that Subregion?

Before posting this i have tried in many ways and I gave up.

Could you please help me here?

Thanks,

QV11 User

[swuehl]

Maybe like this, using a chart with dimension SubRegion and as expression

=Concat( Aggr( Rank( -Order) & '. ' &Type, SubRegion, Type) , ';', Order)

[#5]

Hi Swuehl,

Thanks you so much for reply!

Is there a way to store each SubRegion value into a variable when you select Region.

If you select America then canada value should store into one variable and Washington value should store into one variable etc...

Thanks,

QV11 User

[swuehl]

What do you want to achieve with storing the values into variables?

If you know the exact names and number of your subregions, you can probably create all variables using a filtered expression, but I wouldn't recommend that.

[#7]

Hi Swuehl,

Thanks for Reply!

I have 4 regions and 10 Subregions in my data.

Could you please let me know how to pass each subregion values into a variable when you select Region.

Thanks,

QV11 User