Custom Sort Dimension based on Expression

[#1]

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?

[nilesh_gangurde · Accepted solution]

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

[jonathandienst]

Hi

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

[#4]

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

[nilesh_gangurde]

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

[er_mohit]

try this

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

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

[#7]

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.