topic Inserting Line Breaks in QlikView

Inserting Line Breaks

johnw — Mon, 10 May 2010 19:31:09 GMT

In script, I would like to insert a line break every 74 bytes into a text field that is up to 32000 bytes. Here is my current solution:

LET InsertLineBreaks = '';
FOR I = 0 TO 500
LET InsertLineBreaks = InsertLineBreaks & '&' & chr(39) & chr(10) & chr(13) & chr(39) & '&mid($1,1+' & $(I) & '*$2,$2)';
NEXT
LET InsertLineBreaks = ' ' & mid(InsertLineBreaks,7);

$(InsertLineBreaks(MyText,74)) as MyFormattedText

Although it works fine for my case, it doesn't seem like an elegant solution. I had been using a Visual Basic function to do the same job, but it caused us problems in SR4, so I want a script replacement.

Any ideas on a better solution?

Inserting Line Breaks

rwunderlich — Tue, 11 May 2010 05:50:06 GMT

Try the attached for an idea.

-Rob

data:
LOAD * INLINE [
data
1234567890ABCDEFGHIJKLMNOP
A long line the goes on and on
]
;

SET crlf = 'chr(10) & chr(13)';
SET size = 7; // Break every seven chars

segments:
LOAD
recno() as recno,
iterno() as segkey,
mid(data, ((iterno()-1) * $(size))+1, $(size) ) as segment
RESIDENT data
WHILE iterno() * $(size) < len(data)
;

final:
LOAD concat(segment, $(crlf), segkey) as finaldata
RESIDENT segments
GROUP BY recno
;

DROP TABLE data;
DROP TABLE segments;

Inserting Line Breaks

Tue, 11 May 2010 10:50:34 GMT

I dont have access to QV at the moment, but how about something like:

Load keyfield,

concat(subtext, '&chr(39)&chr(10)&chr(13)&chr(39)') as newtext

group by keyfield;

Load keyfield,

mid(textfield, (74 * (iterno() -1)) +1, 74 * iterno()) as subtext

while iterno() <= lines;

Load keyfield,

textfield,

ceil(len(textfield)/74, 1) as lines

from.....;

Regards,

Gordon

Inserting Line Breaks

johnw — Tue, 11 May 2010 18:37:57 GMT

Heh. When I started adapting both approaches to my example, it became clear that they're actually the same approach, at least when you strip them down to fundamentals. And I agree that this is a better solution than the one I'm using. And in particular, that you both suggested the "same" solution makes me pretty confident in it.

Unforunately, although these scripts behave themselves in 9.0, they get a General Script Error in version 8.5. I tracked this to the combination of concat() with characters chr(10) or chr(13). It refuses to do that in 8.5. I'm guessing it's a QlikView bug that got fixed in 9.0.

I suppose it's no big deal. I have a working emergency fix, and once we upgrade to 9.0, I can replace it with the more elegant approach that you both came up with. Thanks, guys.

Edit: Perhaps I should clarify something. Our server is on version 9.0 SR4, but our individual computers are still on 8.5. So the script must work in both 8.5 and 9.0 for the moment.

Edit: The "Rob & Gordon" solution runs an order of magnitude faster than mine.

Edit: Ah, the solution for 8.5 is simple. Just a single quote, new line, and another single quote. THAT works, since it seems to be the chr() that concat() has a problem with, as opposed to the new line itself.

Inserting Line Breaks

johnw — Tue, 11 May 2010 22:53:27 GMT

OK, here's what I ended up with in context in the actual application. Seems to work. Again, thanks for your help guys.

SET Verbatim=1;

LEFT JOIN ([Cost Field])
LOAD
LPID as "Cost Field Rule ID"
,concat(Line,'
',Sequence) as "Cost Field Rule"
GROUP BY LPID
;
LOAD
LPID
,iterno() as Sequence
,rtrim(mid(LPRULE,74*(iterno()-1)+1,74)) as Line
WHILE iterno() <= ceil(len(rtrim(LPRULE))/74)
;
SQL
SELECT LPID, LPRULE
FROM SYSTLC.$(TransformChar)TLP202R
WHERE LPREV = 9999
AND LPRTYP = 'C'
ORDER BY LPID,LPREV,LPRTYP,LPRSEQ
;
SET Verbatim=0;