topic Re: Custom Sort Dimension based on Expression in QlikView

Custom Sort Dimension based on Expression

Wed, 27 Nov 2013 05:15:12 GMT

Hi - I have created a formula under dimension, like,

if( [Education] = 'Middle School' or [Education] = 'High School', 'High School and below', if( [Education] = 'College', 'College', 'Other'))

If I want to custom sort the defined 'High school and below', 'College', 'Other', how do I do that? I tried to add Expression under the Sort tab, but it doesn't take it. I tried =match( [Education], 'High school and below', 'College', 'Other')

Any suggestions?

Re: Custom Sort Dimension based on Expression

nilesh_gangurde — Wed, 27 Nov 2013 05:19:20 GMT

Hiii,

Create one field with the above if statement.

and then add that field into dimention and write expression in the Sort tab.

for Example:

if([New_Field] = 'High school',1,

if([New_Field] = 'Middle School',2,

if([New_Field] = 'College',3,

if([New_Field] = 'Other',4))))

It will work.

-Nilesh

Re: Custom Sort Dimension based on Expression

jonathandienst — Wed, 27 Nov 2013 05:26:50 GMT

In your script, inline load the values before the main data load using the same field name into a temp table. At the end of the load, drop the temp table. Qv remembers the first load order.

tmp_Education:

LOAD * Inline

[

Education

Middle school

High School

College

Other

];

... load model data here...

DROP Table tmp_Education;

Now use original load order as the sort method.

HTH

Jonathan

Re: Custom Sort Dimension based on Expression

Wed, 27 Nov 2013 07:39:20 GMT

Hi Nilesh,

I'm new to QV... By creating a new field, I edited in script

Load Education,

if( [Education] = 'Middle School' or [Education] = 'High School', 'High School and below', if( [Education] = 'College', 'College', 'Other'))

as Education_new;

After reloading the data, I don't see the new field Education_new in the field list. What did I do wrong? Do I need to specify which file or table it comes from? I do have multiple tables connected with keys.

Thanks a lot!

Carol

Re: Custom Sort Dimension based on Expression

nilesh_gangurde — Wed, 27 Nov 2013 07:47:37 GMT

Yes you can create the new field exactly below the [Education] field, so the new field will take data from the respective table i.e. the table in which [Education] field is present.

For Example :

Load Field_1,

Field_2,

[Education],

if( [Education] = 'Middle School' or [Education] = 'High School', 'High School and below', if( [Education] = 'College', 'College', 'Other'))

as Education_new

From TABLE_NAME;

-Nilesh

Re: Custom Sort Dimension based on Expression

er_mohit — Wed, 27 Nov 2013 07:50:43 GMT

try this

if([Education] = wildmatch( [Education] , 'Middle School' , 'High School'), 'High School and below',

if([Education] = wildmatch([Education] , 'College'), 'College', 'Other'))

Re: Custom Sort Dimension based on Expression

Wed, 27 Nov 2013 09:14:02 GMT

Thanks a lot for your help!

Since I had loaded the table, I had to insert the if statement in the original Load script. I added the if statement to the end of the script, which was wrong.