Loop for Field name

[cbushey1]

Hi there,

I am looking to create a dynamic label based on a field from one of my tables in the script. I have this field [AccountFriendlyName] that is potentially very lengthy. My goal is to create a new field that I can use that is formatted nicer than the original field. I think I am close with my loop but I seem to have an issue with defining a variable to a field name outside of a load.

Here is my code.

Let vNumOfSpaces = SubStringCount(AccountFriendlyName,' ');

Set vLabelAccountName = '';

Set i = 0;

Set vLastPosition = 0;

Do While i < $(vNumOfSpaces)

  Let vIndex = Index(AccountFriendlyName,' ',$(i)) - $(vLastPosition);

  If $(vIndex) < 12 then

  Trace $(vIndex);

  Next i;

  ELSEIF $(vIndex) > 12 then

  Let vLabel_AccountName =  $(vLabel_AccountName) & Mid(AccountFriendlyName,$(vLastPosition),$(vIndex)) & Chr(13);

  Set vLastPosition = $(vIndex);

  TRACE $(vIndex) & '_' & $(vLastPosition);

  Next i;

  ENDIF

Loop;

[cbushey1]

marcus_sommer,

Thank you for this script. I see what you are doing and I think it is very close to working for my purposes. What I have found is that the returned string (ConcatWords) is not in the correct order. It looks like the way the values are stored in SingleWord doesnt store them in order from each IterNo so when the concat happens the values are out of order. I was thinking about using FirstSortedValue to store the SingleWord in the correct order but wasn't sure exactly how to get it to work.

Any thoughts on how I can make sure the SingleWord field is storing the values in the exact order?

[sunny_talwar]

I have not looked at the complete script that Marcus has provided, but Concat() takes a third argument which tells the function to sort in a particular order. May be that is what you need here

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/StringAggregationFunct...

[marcus_sommer]

Here a small adjustment to get the right order within the concat-function which could be sorted:

ConcatWords:

load

    rowno() as RowNo, WordKey, concat(SingleWord, ' ', IterNo) as ConcatWords

resident SplitRecords group by WordKey;

- Marcus

[cbushey1]

Thank you both for your continued help. I can't believe I forgot that concat had a third argument, but thanks for pointing that out.

One thing that I am not sure I understand is now that this concatenation has occurred, it would appear that each line of text is separate within the ConcatWords field. What I mean by this is that if I try to use ConcatWords as either a label in a bar chart (original goal of my request) or as a list box, I can click on either line of text. I would have expected the text to remain as one clickable object or better one field for my bar chart.

Any thoughts as to why this isn't the case?

[marcus_sommer]

It's not quite clear to me what you want to achieve with them. Please share a small example with your objects and maybe a picture of how it should look like.

- Marcus

[cbushey1]

Here is a sample QVW.

[marcus_sommer]

The values needs to include linebreaks to achieve such a multi-line displaying. I had made an adjustement of your example with replace(AccountFriendlyName, ' ', chr(10)) and also adjusted my example with a further load to adapt these logic.

- Marcus

[cbushey1 · Accepted solution]

I found the solution I needed here: Displaying long labels on chart X-axis‌.

I edited Rob's code to better fit my needs since my field "AccountFriendlyName" isnt unique to the dataset.

Thank you to everyone who took the time to work on this problem!

[cbushey1]

Thank you Marcus again for all your help on this!