topic Re: Table box not sorting correctly in QlikView

Table box not sorting correctly

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Table box not sorting correctly

swuehl — Wed, 21 Sep 2016 19:38:18 GMT

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

Re: Table box not sorting correctly

Anonymous — Wed, 21 Sep 2016 19:40:31 GMT

Hi Stefan,

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

Re: Table box not sorting correctly

swuehl — Wed, 21 Sep 2016 19:51:55 GMT

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

Re: Table box not sorting correctly

Anonymous — Wed, 21 Sep 2016 19:56:59 GMT

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??

Re: Table box not sorting correctly

swuehl — Wed, 21 Sep 2016 19:59:20 GMT

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)

Re: Table box not sorting correctly

Anonymous — Wed, 21 Sep 2016 19:59:20 GMT

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 !!

Re: Table box not sorting correctly

Anonymous — Wed, 21 Sep 2016 20:02:54 GMT

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?

Re: Table box not sorting correctly

swuehl — Wed, 21 Sep 2016 20:08:46 GMT

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

Re: Table box not sorting correctly

Anonymous — Thu, 22 Sep 2016 12:21:40 GMT

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!