topic Re: Wrapping the field values at script level in QlikView

Wrapping the field values at script level

sudeepkm — Wed, 06 Aug 2014 14:10:25 GMT

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

2 abc

dre

ftt

3 abd

csd

fgt

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.

Re: Wrapping the field values at script level

JonnyPoole — Wed, 06 Aug 2014 15:40:32 GMT

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

Re: Wrapping the field values at script level

evan_kurowski — Wed, 06 Aug 2014 23:02:51 GMT

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

Re: Wrapping the field values at script level

MarcoWedel — Wed, 06 Aug 2014 23:54:02 GMT

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

Re: Wrapping the field values at script level

JonnyPoole — Thu, 07 Aug 2014 01:07:37 GMT

Short and sweet

Props to Marco Wedel: Kick Ass!

You kick ass!

Re: Re: Wrapping the field values at script level

evan_kurowski — Thu, 07 Aug 2014 03:14:53 GMT

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

Re: Wrapping the field values at script level

rwunderlich — Thu, 07 Aug 2014 03:48:28 GMT

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

MarcoWedel — Thu, 07 Aug 2014 08:02:24 GMT

Many Thanks

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

Re: Wrapping the field values at script level

sudeepkm — Thu, 07 Aug 2014 10:10:29 GMT

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.

Re: Wrapping the field values at script level

sudeepkm — Thu, 07 Aug 2014 10:36:40 GMT

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.

Re: Wrapping the field values at script level

evan_kurowski — Thu, 07 Aug 2014 12:40:13 GMT

Sudeep Mahapatra wrote:

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

I'm not sure if I see the values of newaddress changing, but they don't enforce sort according to the input sequence. If you want to enforce FIFO, I added that.

(i.e. address = 1AU2BE3CO4HE

newaddress = 1AU

2BE

3CO

4HE)