×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to count fact table records by comparing to numeric values in the

How to count fact table records by comparing to numeric values in the

How to count fact table records by comparing to numeric values in the

(OP)
I need a count of records from my fact table based on a condition.

I create param_val fact from my fact table F1.
One of my dimensions is A1 (based on A1 table having columns Low and High)
For each record of the fact table, this param_val needs to be compared to Low and High.
I need to count all records which have Low < param_val <High

For this, I have created facts Low (From the dimension table A1) and High(from the dimension table A1)
Metrics LowM1 and HighM1 have been created as min(Low) and min(High)
(Here, also, is there any other way where I can get the absolute value of the column for a particular record. This use of min gets very messy on changing levels of attributes displayed on the report.)

I am creating a filter with set qualification for the mentioned condition.

Somehow, this doesn't work.
Please let me know how to go about ?

RE: How to count fact table records by comparing to numeric values in the

2 quick questions:
- Is the sql you want something like this?

select count(F1.param_value), abc
from F1, A1
where F1.param_value>A1.low
and F1.param_value<A1.high
and F1.xyz = A1.xyz
group by abc

if so, what is xyz and what is the abc you grouping by?

there's an ABS function you use instead of max or min.

RE: How to count fact table records by comparing to numeric values in the

(OP)
Yes, the sql would look like this. But, i don't want abc to be displayed on the report.
I would use this count to arrive at yield and would want to display it week vs parameter (which are other two dimensions linked to the fact table)
Also, that ABS function gives problems.

RE: How to count fact table records by comparing to numeric values in the

here are some quick ideas for you to try since I don't have all the info clearly:

1) yield sounds like it is a ratio, and this count is in the denominator.  this is fairly straightforward by using compound metrics
2) since abc is on report, and not used in your yield report with parameter and week, then you should not group by abc at all. I would suggest creating a metric with a filter in it.
3) you might have to use dimensionality of the metric to force the sql to run at the f table level.

this is a hard problem, i'm not sure but you might want to check with one of their consultants or tech support...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close