Trim values in tables

[JustinDallas]

Hello Everyone,

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?

[ramoncova06]

can't you apply a rtrim on your query ?

[Gabriel]

Hi,

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.

Hope this helps

[JustinDallas]

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.

[ramoncova06]

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

[rwunderlich]

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 ... ;   

DROP Field %ShipTo;   

RENAME Field %TrimmedShipTo TO %ShipTo 

[JustinDallas]

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.