Filter Ordering of Numeric Values

jim fuller shared this question 15 months ago
Answered

Hi folks, this is probably a how-to question.

I'm running the following YF:/GH9QAAAABJRU5ErkJggg==


My data source is a sql statement in postgres db. One column in the statement is this:

/IAAAAASUVORK5CYII= -- 0 length precision means no decimal value.

However, when I drop this field into the filter well in report builder, then use format to change filter style to cached list, then update the list values. The sort treats the numeric as a character and sorts in alphanumeric instead of numeric order.

Here's the list of values generated by YF's filter values:

/wNmwxfQb7g9AQAAAABJRU5ErkJggg==


Thanks for you help in advance.

Comments (3)

photo
1

Hi Jim,

so far I haven't been able to reproduce this issue - my postgres numeric values are ordering themselves as numbers when in a Yellowfin filter (and I'm using the same build as you, 7.4 20171204).

So I thought I'd better check my steps with you to make sure they are OK:

1) I started off with an int column ("c_int") in a table "T1" in my Postgresql database

/h9bFC8GM6KkuwAAAABJRU5ErkJgggA=


/AU1MWDMu23frAAAAAElFTkSuQmCCAA==

2) then I created a postgresql view by running the following statement in the Postgresql db

CREATE VIEW FilterOrderingIssue 
AS
SELECT c_date, c_int::numeric(2,0), name, c_uuid 
FROM t1

which yielded the following:

/9PEZIMPkib8AAAAAElFTkSuQmCCAA==

notice how initially the column "c_int" was DATA_TYPE 4 which according to the ANSI standard is "Integer"

Then after I converted it in the Postgresql view the DATA_TYPE became 2 which is "Numeric"

In the Yellowfin view it recognised it as Numeric, and I changed it from a Metric to a Dimension and then it was fine in the report as a filter:

/49v7Dp48AgGejnQAtHzvt5PlfJACAK6OdYBZoJwAwIbQTzALtBAAmhHaCWaCdAMCE0E4wC7QTAJgQ2glmgXYCABPyB6hMk92NKPZzAAAAAElFTkSuQmCCAA==

/8HXexlCSulg2gAAAAASUVORK5CYIIA


If you can see anything about the above steps that is wrong can you please tell me.

Also, it would be great if you could find out the ANSI SQL DATA_TYPE code for your column and let me know.

I use DBVisualizer to get this information, I'm not familiar with other DB utilities but I guess they'd all have this information somewhere.

thanks,

David

photo
1

My apologies about the Delay Big Dave.

I removed the filter and added it back. Seemed to work.

I think changing types in the datasource doesn't autmatically adjust the existing filters to new type.

No biggie.. You can close said ticket.


Kindest Regards.

photo
1

OK Jim, well I'm glad it's all working now, thanks for letting me know!

regards,

David