Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sudeepkm
Valued Contributor 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.

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Wrapping the field values at script level

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

10 Replies
Employee
Employee

Re: Wrapping the field values at script level

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
Valued Contributor

Re: Wrapping the field values at script level

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

Re: Wrapping the field values at script level

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

Employee
Employee

Re: Wrapping the field values at script level

Short and sweet


1398983351.png
You kick ass!
evan_kurowski
Valued Contributor

Re: Re: Wrapping the field values at script level

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

Re: Wrapping the field values at script level

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

Re: Wrapping the field values at script level

Many Thanks

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

sudeepkm
Valued Contributor III

Re: Wrapping the field values at script level

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
Valued Contributor III

Re: Wrapping the field values at script level

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.

Community Browser