Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Verbatim, but only keep leading spaces, remove trailing spaces

Is there any way to use Verbatim but have it only apply to leading spaces? It seems that our ERP software recognizes leading spaces, but it removes the trailing spaces. This is causing a lot of linking issues wether or not I use Verbatim. I know that I could use Verbatim and then go and add rtrim() to every single field but that would be extremely cumbersome.

Tags (2)
8 Replies
Not applicable

Re: Verbatim, but only keep leading spaces, remove trailing spaces

Ok if such a thing does not exist, does anyone know if adding rtrim to the fields in the Select section auto applies that to all instances in the Load section?

Re: Verbatim, but only keep leading spaces, remove trailing spaces

Yes. If rtrim() is used in the SELECT, the value received by the LOAD will already be trimmed.

-Rob

Not applicable

Re: Verbatim, but only keep leading spaces, remove trailing spaces

Alright, thank you. That makes what I might have to do slightly less daunting. Still would be awesome if there was just a simple variable though.

Not applicable

Re: Verbatim, but only keep leading spaces, remove trailing spaces

Since there is no variable to apply Rtrim to a document, does this mean I lose the ability to do say:

Select * From ...?  I am aware that you cannot do rtrim(*) and that it needs applied to every field, then every field needs to be labeled.

so the following needs done for every field selected?

Rtrim(field) as field,

I am now loading entire tables to QVD's using incremental load with the * function so that I do not need to waste space by pulling every field. This however stores based on the document so I would have to pull every field for the stored table and do rtrim(field) as field,  for every field to apply this correctly.

This just seems kind of ridiculous to have to do. Also, could applying trim functions affect index time?

Re: Verbatim, but only keep leading spaces, remove trailing spaces

You could build an load statement in a loop that does the rtrim() for every field. But back to the orginal problem. You said your ERP system removes the trailing spaces. So you are pulling data from two sources, these tables and the ERP system that treat the same fields differently

-Rob

Not applicable

Re: Verbatim, but only keep leading spaces, remove trailing spaces

Our ERP system stores whatever was entered into the field into the database. From within the ERP system however it disregards the trailing spaces when it determines the links between programs and uniqueness of fields. How would a load statement like that look? I am loading around 35 tables on my largest document, where most of them are stored into qvd's for other documents to reference.

Re: Verbatim, but only keep leading spaces, remove trailing spaces

You could code up a script SUB like this:

SUB RtrimTable (_table)

          SET vComma=;

          SET vLoad=;

          FOR i = 1 to NoOfFields('$(_table)')

                    LET vField = FieldName($(i),data);

                    LET vLoad = '$(vLoad) $(vComma)rtrim([$(vField)]) as $(vField)';

                    SET vComma=,;

          NEXT i

 

          $(_table)_temp:

          NoConcatenate

          LOAD

                    $(vLoad)

          RESIDENT $(_table)

          ;

 

          DROP TABLE $(_table);

          RENAME TABLE $(_table)_temp TO $(_table);

END SUB

And then assuming your loaded table is named "data", call the sub like this:

CALL RtrimTable('data')

That will reload table data with an rtrim on every field and replace the data table.

Attached is an example.

-Rob

http://robwunderlich.com

Not applicable

Re: Verbatim, but only keep leading spaces, remove trailing spaces

Is there a way to do this so it does not apply to date fields? doing this seems to screw the data up.

Community Browser