Filter Ordering of Numeric Values
Answered
Hi folks, this is probably a how-to question.
I'm running the following YF:
My data source is a sql statement in postgres db. One column in the statement is this:
-- 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:
Thanks for you help in advance.
The same question
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
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 t1which yielded the following:
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:
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
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
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 t1which yielded the following:
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:
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
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.
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.
OK Jim, well I'm glad it's all working now, thanks for letting me know!
regards,
David
OK Jim, well I'm glad it's all working now, thanks for letting me know!
regards,
David
Replies have been locked on this page!