topic Re: Trim values in tables in QlikView

Trim values in tables

JustinDallas — Mon, 20 Apr 2015 20:35:13 GMT

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?

Re: Trim values in tables

ramoncova06 — Mon, 20 Apr 2015 20:42:06 GMT

can't you apply a rtrim on your query ?

Re: Trim values in tables

Gabriel — Mon, 20 Apr 2015 20:46:39 GMT

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

Re: Trim values in tables

JustinDallas — Mon, 20 Apr 2015 20:55:27 GMT

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.

Re: Trim values in tables

ramoncova06 — Mon, 20 Apr 2015 21:10:37 GMT

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

Re: Trim values in tables

rwunderlich — Mon, 20 Apr 2015 21:35:34 GMT

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

Re: Trim values in tables

JustinDallas — Mon, 20 Apr 2015 21:43:29 GMT

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.