Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sudeepkm
Specialist III
Specialist III

Wrapping the field values at script level

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.

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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:

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

address.PNG.png

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

View solution in original post

10 Replies
JonnyPoole
Employee
Employee

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:

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

address.PNG.png

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
Specialist
Specialist

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

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

];

QlikCommunity_Thread_128531_Pic1.JPG.jpg

hope this helps

regards

Marco

JonnyPoole
Employee
Employee

Short and sweet


1398983351.png
You kick ass!
evan_kurowski
Specialist
Specialist

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

20140806_text_field_chop_v2.png

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Specialist III
Specialist III
Author

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.

T128531.png

sudeepkm
Specialist III
Specialist III
Author

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.