topic Re: Dynamic Sort value using IF in QlikView

Dynamic Sort value using IF

Wed, 27 Jan 2016 16:25:00 GMT

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

Re: Dynamic Sort value using IF

Colin-Albert — Wed, 27 Jan 2016 16:40:43 GMT

Define the Type field as a dual value with the text and numbers in the order you want and the field will then sortas you require.

Dual function

Re: Dynamic Sort value using IF

rwunderlich — Wed, 27 Jan 2016 16:52:08 GMT

In the Pivot table in your example, use the expression:

=Type & '-' & RowNo()

-Rob

Re: Dynamic Sort value using IF

Wed, 27 Jan 2016 17:33:57 GMT

Hi Rob,

Thanks for your reply!

Actually my requirement is I need to pass Sort values dynamically in UI level.

For example if I click on AMS first it should start loop like if Diamond is available make it as 'Diamond-1'

Next it should search Gold if it's not there then it should search for Platinum then value as 'Platinum-2'

like that it should work.

I think using If it's possible but i'm not expert in creating IF loops

Thanks,

QV 11 User

Re: Dynamic Sort value using IF

rwunderlich — Wed, 27 Jan 2016 18:28:10 GMT

I don't understand. You already have the correct sort order as alphabetical. Where in the UI do you want to display "Diamond-1"?

-Rob

Re: Dynamic Sort value using IF

Wed, 27 Jan 2016 19:01:13 GMT

Hi Rob,

If user selects Americas then three values for Region should store in a variable like as

" Diamond-1,Platinum-2,Silver-3 "

If user selects Europe then three values for Region should store in a variable like as

" Diamond-1,Gold-2,Silver-3 "

Thanks in advance,

QV11 User

Re: Dynamic Sort value using IF

rwunderlich — Thu, 28 Jan 2016 00:02:14 GMT

I'm not sure where you want the display to be. Can you mark up a QVW or a drawing?

-Rob

Re: Dynamic Sort value using IF

swuehl — Thu, 28 Jan 2016 00:19:26 GMT

Maybe like attached?

edit: updated sort order to be more intuitive

Re: Dynamic Sort value using IF

Thu, 28 Jan 2016 08:23:57 GMT

Hi Swuehl,

Thank you very much for your time and solution.

This what exactly what i am expecting.

I'm getting lot of support & Information over community.

Once again Thank you very much.

Thanks,

QV11 User

Re: Dynamic Sort value using IF

mohan2391 — Thu, 28 Jan 2016 09:11:38 GMT

Hi,

Can u please explain here itself what you have done in above QVW. We have personal edition only here.

Thank u

Re: Dynamic Sort value using IF

swuehl — Thu, 28 Jan 2016 18:12:10 GMT

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)

Re: Dynamic Sort value using IF

Sun, 31 Jan 2016 10:01:10 GMT

Hi Swuehl,

Can you extend your valuable help please?

I have data like as below

Table1:

LOAD * Inline [
Region,Type,SubREgion
Asia,Gold, India
Asia,Diamond,India
Asia,Platinum,China
Asia,Silver,India
America,Diamond,Washington
America,Platinum,Canada
America,Silver,Washington
Europe,Gold,Ireland
Europe,Diamond,France
Europe,Silver,Ireland
];

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

Re: Dynamic Sort value using IF

swuehl — Sun, 31 Jan 2016 13:29:53 GMT

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

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

Re: Dynamic Sort value using IF

Sun, 31 Jan 2016 14:44:26 GMT

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

Re: Dynamic Sort value using IF

swuehl — Sun, 31 Jan 2016 15:47:54 GMT

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.

Re: Dynamic Sort value using IF

Sun, 31 Jan 2016 15:56:29 GMT

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