flagging inconsistent text fields based on previous row

[jeffreygreen]

I have a report I run and i sort this list by Part number. I have an SPI column next to my part number and all similar part numbers should have the same SPI indicator (usually "MX" but could be a few others . . . so i will just say if "row" 15 is populated at all, and row 16 has the same part number, then it too should be populated. I do a similar thing with the PRICE field by using RUNNING_DIFFERENCE of PRICE for PART NUMBER and it works great, but how do I do a similar thing with a text field?

Thanks

 

[blom0344]

Well, not with a running aggregate since these will only yield numerical values.
You basically want to add missing attributes to keys that have non defined. That should not be handled within the report, but in the source (app db or etl/dwh)

A report will not 'populate' values, but sometimes there is a workaround. To me though it looks like you source data lacks quality

Ties Blom

 

[jeffreygreen]

can't I maybe add a calculated query that says "if SPI defined, then 1, Else 0" then do my running difference for part number off of that?

 

[blom0344]

If row 15 and row 16 have identical partnumbers, then it would be a matter of using a second query in a join to bring in the SPI value. However I cannot understand why the partnumber in row 15 would have an SPI value and the one in row 16 not..

Ties Blom

 

[jeffreygreen]

Blom - this data is all related to things that come through Customs for one of the big 3. If the SPI is defined, usually MX or CA for NAFTA, then the part is Duty Free. If there is no SPI for that part, then there is duty on the part. Our brokers at the border, ASSUMING THEY KNOW WHAT THEY ARE DOING, enter the correct SPI when the part crosses the border. The brokers, a lot of times, don't enter the SPI and we get stuck paying duty. My purpose in trying to write this report is to find the parts that have SPI's one day, and not on the other. etc.

 

[blom0344]

Which would be a reason to believe there is a master record somewhere storing the correct partnumber - SPI combination. The brokers at the border must have some way of knowing which SPI should belong to a Partnumber.
And even without this master record, you could generate the relation from factdata elsewhere, based on the occurence of SPI for each partnumber.

Another solution would be to write a small stored procedure that loops through the data assigning SPI values based on previous value.

However, suppose row 15 would have an incorrect SPI , you wouldn't want to replicate that value to the next row would you?

Ties Blom

 

[jeffreygreen]

The table containing the correct SPI for that part is not stored in COGNOS. I, personally, don't even know what the SPI is supposed to be for a given part.
And, yes, the brokers are given a table that contains the correct SPI to use, but they constantly fail to use the right one.
All I am being asked to do is identify records where an SPI is being inconsistently applied to the same part in the same time period, such as a month. So I run this report, for the month of all part numbers imported (each is entered dozens of times), then I sort by Part number and import date. Sometimes we get proof of origin in the middle of a month, so I expect to see.
MX
MX
MX
MX
ETC.
OR
NULL
NULL
NULL
MX
MX
MX
ETC.
What I DON'T want to see is this...

MX
null
null
MX
MX
null
etc.

I don't know if MX is correct, or NULL is correct, but I forward this report to people that do.

 

[blom0344]

I am in no position to judge your assigned job, but to me it looks like a totally inept use of a BI tool like Cognos.
Since the brokers have the correct master data all you would need is the same data in a table to make sure you can create a meaningful screening report. The report would then be capable of pointing out needed corrections.

It is not a matter of identifying the records that point to inconsistency. It looks like a matter of identifying partnumbers that are coupled to more than 1 SPI code.

In Cognos this would require an additional query yielding those partnumbers with more than 1 distinct value for SPI (including null values) :

In SQL terms this would be something like:

SELECT PARTNUMBER,
COUNT(DISTINCT(CASE WHEN SPI IS NULL THEN 'MISSING' ELSE SPI END)) AS #_SPI
FROM ....
GROUP BY PARTNUMBER

Ties Blom

 

[jeffreygreen]

Thanks, I will try that.

I do agree with you that my company needs to bring the table? containing the correct value, but for now, they don't and this is the only solution I have to offer. It is minimal effort for me to do this "inconsistent SPI" report and then forward the output to the proper team.

THanks for your help, I will try this today and let you know how it goes.