Wrapping the field values at script level

[sudeepkm]

Hi,

I've a table with fields like below

id     address

1      abcdefr

2      abcdreftt

3      abdcsdfgtr

4      abbqweds

5      abbcddfgf

6      acvbjhgfdd

I would like to wrap this dynamically for each 3 chars of the field address.

I can have a solution like below but need to do it dynamic.

if(len(address)>3,left(address,3)&chr(10)&right(address,len(address)-3)) as newaddress

the newaddress will have values like below

id    newaddress

1     abc

       defr

2    abc

      dreftt

3    abd

      csdfgtr

but what I'm trying to achieve is like below

id    newaddress

1    abc

      def

      r

2    abc

      dre

      ftt

3    abd

      csd

      fgt

      r

I'm creating alerts in QlikView. Since the alerts Subject panel does not have format options or options for embedding charts/tables so I'm trying to create a table like format for the data to be put in the Alert Subject section. Some lengthy field values are creating trouble so I'm trying to wrap them.

Any suggestions please. Thanks in advance.

[JonnyPoole · Accepted solution]

Although its not going to be very efficient from a reload perspective, i couldn't resist creating a script to do this:

see below and a pasted screenshot and app:

------------------

SourceAddressTable:

LOAD

  RecNo() as RecNumber,

  address as Address

FROM

(ooxml, embedded labels, table is Sheet1);

MaxRecNumberTable:

Load

  max(RecNumber) as MaxRecNumber

resident SourceAddressTable;

let vNumRecords= peek('MaxRecNumber',0,'MaxRecNumberTable')-1;

FOR a=0 to $(vNumRecords)

  let vAddress=peek('Address',$(a),'SourceAddressTable');

  let vAddressLength=len(vAddress);

  if vAddressLength> 3 then

  let vAddressLines=floor( vAddressLength/3);

  let vNewAddress=mid(vAddress,1,3);

  For b=2 to vAddressLines

  let vNewAddress= vNewAddress & chr(10) & mid(vAddress,(b*3)-2,3);

  next b;

  let vNewAddress= vNewAddress & chr(10) & mid(vAddress,vAddressLines*3);

  ELSE

  let vNewAddress=vAddress;

  end if

  NewAddresses:

  load

  RecNumber,

  '$(vNewAddress)' as NewAddress

  resident SourceAddressTable

  where RecNumber=$(a)+1;

NEXT a

[evan_kurowski]

If you don't mind sourcing from a text file, this method could work.

[MarcoWedel]

Hi,

another method might be the usage of preceding loads to first split the strings (using IterNo()/While) and then recombine them with the linefeed as seperator (using concat()):

LOAD id,
    address,
    Concat(addrLine, chr(10)) as newaddress
Group By id, address;
LOAD *,
    Mid(address, IterNo()*3-2 ,3) as addrLine
While IterNo()<=Ceil(Len(address)/3);
LOAD * INLINE [
    id, address
    1, abcdefr
    2, abcdreftt
    3, abdcsdfgtr
    4, abbqweds
    5, abbcddfgf
    6, acvbjhgfdd
];

hope this helps

regards

Marco

[JonnyPoole]

Short and sweet

Props to Marco Wedel: Kick Ass!

---

You kick ass!

[evan_kurowski]

Marco, that use of the IterNo() and Len() combination is brilliant, and it does help.  I'm gonna borrow that here:

[rwunderlich]

If you need to split at a word boundary or maximum length, here's a solution I posted some time back.

Displaying long labels on chart X-axis

-Rob

[MarcoWedel]

Many Thanks

Though I'm quite sure, Chuck would have needed only one line of code for the same task ...

[sudeepkm]

Thanks a lot. wonderful solution.

Just one thing which I found is that when I use spaces in the values of the Field address the newaddress field is changing.

[sudeepkm]

Thanks a lot to everyone for their help and valuable suggestions.

Unfortunately I'm not able to select multiple answers as Correct so marking helpful.

Sadly I'm not able to use the solution as the QlikView Alert still does not take this format as it is.

I thought the alert email will now show it as it is structured like below

id     address    other fields

1      abc             other1

         defr

2      abc             other2

         dre

         ftt

3      abd             other3

        csd

        fgtr

but the output was like below.

id     address    other fields

1      abc

defr    other1

2      abc             other

dre   ftt         other2

3      abd

csd fgtr      other3

its been three years I'm using alerts but there has not been enough format options enhancement done to the alert's  subject panel unless I'm missing something. I think this could be because of PDF distribution of report being available. In my case our environment is not using PDF distribution and I'm trying to find a solution using the Alert feature.

Thanks again everyone.