So I have some tables with a field "%ShipTo". Sometimes the field has insignificant white space on it's right and it keeps the keys from matching up and linking like it should. For example, a value could be "Something/Else/Here ". Currently, I am cleaning up my data model with a script that looks like this.
RENAME TABLE Fact TO SpacedFact
;
RENAME TABLE ShipTo TO SpacedShipTo
;
Fact:
LOAD * , RTRIM(%ShipTo) as %TrimmedShipTo Resident SpacedFact
;
ShipTo:
LOAD * , RTRIM(%ShipTo) as %TrimmedShipTo Resident SpacedShipTo
;
DROP Field %ShipTo From Fact
;
DROP Field %ShipTo From ShipTo
;
DROP Tables SpacedFact,SpacedShipTo
;
RENAME Field %TrimmedShipTo TO %ShipTo
;
As you can see, this script is kind of verbose when the only goal is to trim white space. My question is, is there a better way for me to accomplish such a simple task?
What I would be thinking in this case is, what if the space is leading or trailing. In that case what I would do is to use TRIM(). This way you catch both leading and trailing white space.
The issue is that this %ShipTo property can be brought from multiple query sources. So while I could just do it in the SELECT * ..., it would be difficult to track them all down. By doing it at the end of the data load, I know that no more %ShipTo fields will be created in the data model.
makes sense now, I don't really see another option then
I was thinking on maybe creating a subroutine that trims all the fields, but that would just extra overhead and complexity to the transformation process
I don't get he need for the additional SpacedFact and SpacedShipTo tables. I think the easiest way may be to specifiy all fields (instead of *) when you load the tables (from QVD or SQL) and do the rtrim(%ShipTo) there.
If you want to continue to use LOAD *, like from a QVD then I still think you don't need the extra tables.
Fact:
LOAD * , RTRIM(%ShipTo) as %TrimmedShipTo FROM ....;
ShipTo:
LOAD * , RTRIM(%ShipTo) as %TrimmedShipTo FROM ... ;
The reason I had the Spaced- stuff was because I needed to take data from one bucket, put it into another to modify it, and then put the modified data back into the original bucket. The end result is that I have a shell-game of sorts for a data load.
