Table box not sorting correctly

[Anonymous]

Hi,

I have a table box that looks like this.

but I need the data to be presented like this without showing the "FiscalMonthVal" column to the user. 

I tried using the Sort -> Expression on "FiscalMonthVal" but it doesn't sort properly.

Can someone help or offer some suggestions?

Thanks

[swuehl]

You could create your FiscalMonth values as dual values in your script using

dual(FiscalMonthName, FiscalMonthVal) as FiscalMonth

then this new field should sort properly

[Anonymous]

Hi Stefan,

I'm new to QV.  Could you provide a bit more details (or a snippet would certainly be great)?

[swuehl]

I assume the three fields in your table box are part of your load script, add the line with dual():

LOAD FiscalYear,

         FiscalMonthName,

         FiscalMonthVal,

         Dual(FiscalMonthName, FiscalMonthVal) as FiscalMonth

FROM ...;

Data Types in QlikView

[Anonymous]

OK, I modified my load script to include the dual() just like you showed it in above.

It still doesn't sort properly.  However if I include "FiscalMonth" in the display as one of the fields, then

it's fine.  Am I missing something??

[swuehl]

Sorry I a was not clear in my suggestion, I would replace FiscalMonthName and FiscalMonthVal with the new field in the table box (so you only show FiscalYear and FiscalMonth)

[Anonymous]

I apologize.  In looking at your dual() again, I realize I had the columns switched.  It should have been

dual(text, number).  It's working now.  Thanks for the tip !!

[Anonymous]

I'm glad it's working but I don't quite understand how QV sorts though. 

If this is what I ultimately end up having using the dual()

JUL1

AUG2

SEP3

Then does the sort -> expression actually evaluates only the number portion of the data?

[swuehl]

You shouldn't need to use a sort expression at all, just use a numeric sort.

[Anonymous]

OK.  I see how the dual()  function is being used now.  I'm still displaying the text (JUL/AUG ...) on the screen

but QV will sort on the "number" portion of this data.  Nice tip!