topic Re: Split string in multiple lines in bar chart dimension in QlikView

Split string in multiple lines in bar chart dimension

Mon, 26 Aug 2013 19:39:07 GMT

Hi All,

I am having a requirement in one of my qlikview report.

I have a bar chart with expression as sum(amount) and dimension as DQ_Issue.

The DQ_Issue field will contain long string values(upto 200 characters with space between words).

So if I use DQ_Isssue as dimension the value won't be visible completely in the graph.

I want to split the long string to multiple lines and then keep as a dimension in the bar chart.Also it should be alighed at centre

How I can i split the string?

Please suggest your ideas if any.

Thanks in advance.Any help would be greatly appreciated

Thanks and Regards,

Praveena

Re: Split string in multiple lines

rustyfishbones — Mon, 26 Aug 2013 19:44:35 GMT

Try using SUBFIELD Function

Subfield (DQ_Issue, 2) AS DQ_Issue

Re: Split string in multiple lines in bar chart dimension

Mon, 26 Aug 2013 19:50:47 GMT

Hi Alan,

Thanks for the quick reply. subfiled is not working.

Re: Split string in multiple lines in bar chart dimension

rustyfishbones — Mon, 26 Aug 2013 19:55:40 GMT

Hi,

Are you trying to do this in the Script or as a calculated Dimension?

Re: Split string in multiple lines in bar chart dimension

rustyfishbones — Mon, 26 Aug 2013 20:03:55 GMT

sorry it should be

SUBFIELD(DQ_Issue,' ',2) as DQ_Issue,

The result will find a space in the string and return the second value,

You could also use the ones below and see what works best for you

SUBFIELD(DQ_Issue,' ',1)

SUBFIELD(DQ_Issue,' ',3)

SUBFIELD(DQ_Issue,' ',5)

Or you could use

RIGHT(DQ_Issue),19) which will give the last 19 characters of the string

LEFT(DQ_Issue),19) which will give you the first 19 characters in the string

or you could try a mixture of both

LEFT(RIGHT(DQ_Issue),19),100)

or try MID()

There are a lot of ways you can try get the result you require, send me a sample line from your document and I will try help you further

Regards

Alan

Re: Split string in multiple lines in bar chart dimension

Anonymous — Mon, 26 Aug 2013 20:20:31 GMT

As Alan says, there are many ways to wrap the field value. I can in addition suggest a replace() function:

replace(DQ_Issue, ' ', chr(10))

It will replace each space with "line feed".

But it's not helpful for a bar chart, because only the first line will be shown. Maybe there is a solution which I don't know yet...

I recommend to use staright table, where you have to use the next two features:

1. Wrap Cell Text (it is on Presentation tab).

2. For expression, use representation "Linear Gauge" and configure it to look like bar, one segment filled to value.

Same can be done with list box with expression

Regards,

Michael

Re: Split string in multiple lines in bar chart dimension

Mon, 26 Aug 2013 20:22:55 GMT

Hi Alan,

Thanks for the reply.

Still how we can split the string in different lines

for example one of my DQ_Issue string is 'MISSING BILL TO CUSTOMER ID IVC STATUS CONTRA FLAG'

I want to split it like

MISSING BILL TO

CUSTOMER ID

IVC STATUS

CONTRA FLAG'

Re: Split string in multiple lines in bar chart dimension

rustyfishbones — Mon, 26 Aug 2013 20:54:29 GMT

I created a Document by bringing in MISSING BILL TO CUSTOMER ID IVC STATUS CONTRA FLAG

and it would look like this in the Script to get the result you want, however the below will only work for that one sentence

If these responses are are helpful, mark them as such, as others will need to find the correct or helpful answers

Re: Split string in multiple lines in bar chart dimension

michelfeit — Wed, 23 Oct 2013 10:07:28 GMT

Hi Michael,

I have the same problem and had the same idea like you had to use a linear gauge as a workaround.

But unfortunately if you set the start values of the segments dynamically by using functions, these functions wont show any dependancy to the dimension values. i.e. you will get the same total-value in each line of your chart.

Any ideas or solutions with that?

Thank You

Markus

Re: Re: Split string in multiple lines in bar chart dimension

rwunderlich — Tue, 12 Nov 2013 02:40:20 GMT

I worked on this some time back,

http://community.qlik.com/message/211766#211766

and the best I could come up with was splitting the strings in the script and displaying on the diagonal. Attached is an example.

I've got this on the list of things to add to QVC.

-Rob

http://robwunderlich.com

Re: Split string in multiple lines in bar chart dimension

mark_casselman — Tue, 12 Nov 2013 08:24:36 GMT

Rob,

Very nice workaround! It works perfectly.

Two suggestions on the script:

I get a red underline in the script on the line

concat(Split, chr(10), SplitId) as $(vSplitField)

I think it should be

concat(Split & chr(10)) as $(vSplitField)

And line

GROUP BY Title

Should be

GROUP BY $(vField)

Regards,

Mark