Case Statement to provide a value for a given intersection of fields

[ggustafson]

Good afternoon,

Thank you for any help you can offer. I'm still fairly new to Cognos, so I apologize if this has been answered before. I wasn't sure what terms to even search for my answer. =P

I'm working with a relational data source and trying to create a report that groups item statistics by buyer. The problem I'm encountering is that our system only stores the correct buyer value for one of our warehouses. All other instances, a default value is used. Hopefully I've described this in enough detail. I've tried attaching a screenshot of some sample data to better illustrate the situation, but I keep getting an error message when I try to post; "Cannot access attachments upload path!".



Is there a way to write a case statement, or is there another function I should be using, to store the buyer_num when whse_code=500? To basically create my own version of buyer_num for all rows that I could use to segment/group the data?

[bus_pass_man]

This is a modelling problem and really ought to be dealt with by your organization's modellers, not least because your organization will need to have a single version of how to deal with this situation and it is best to do that in one place.  In the short run, in the model, if you can't do it in your ETL process.  This is because it is possible that there are reports and dashboards which will not have the correction built into them or incorrectly done or done inconsistently with others.  In the long run, your organization really needs to think about fixing your systems so bad data isn't being introduced.

If I understand correctly this is the situation:

You want to lookup the records in your sales fact table and, where the warehouse code value indicates that the sale is associated with a buyer number value which is incorrect, substitute (somehow) correct buyer number values.  Is that a correct understanding?

Where are the warehouse code and buyer number columns stored?  In the fact table?

How will you know what the buyer numbers will be for the things where the incorrect buyer numbers are stored? How will be get those (correct) values?

[ggustafson]

I agree, and I've recommended to my organization that we correct the process that is storing irrelevant values tied to our item movement.

In the interim, I decided to create a separate query for the one warehouse with correct values to query the buyer_num associated with each item_num, then join it to the other table with item_num as the link to spread that value to all other warehouses.

Thank you for taking the time to reply. I appreciate it.