Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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
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.
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;