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: 
cbushey1
Creator III
Creator III

Loop for Field name

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;

1 Solution

Accepted Solutions
cbushey1
Creator III
Creator III
Author

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!

View solution in original post

18 Replies
sunny_talwar

See if this helps you. One of my colleague created this and I thought it was pretty impressive. See if this helps you.

Best,

Sunny

cbushey1
Creator III
Creator III
Author

Hi Sunny,

Thanks for the quick reply. I can see where you colleague was going with this but I am looking for something a little more dynamic. They hard-coded each substringcount command up until 7. In my case I know I have some fields that go up to 12 but I really want to explore this using variables and a loop.

I think this method could work, but it is not ideal for my situation.

sunny_talwar

If you can provide a sample of data, we can play around with it to make it work.

cbushey1
Creator III
Creator III
Author

Unfortunately I cant share the data I am using for AccountFriendlyName. But you can create your own sample data for this one field pretty easily.

I know I have some fields that have no spaces and others that have any number of spaces.

I listed my loop code so that should get you started.

thanks again.

sunny_talwar

I might create dummy data which won't work for you, if you can provide dummy data which is close to your data we won't have to go through the pain of re-doing this multiple times

cbushey1
Creator III
Creator III
Author

Here is a file you can use. Again the words dont matter (these arent even english) but you get the idea.

(Not sure why I couldnt attach this, but consider each row 1 record).

!

 

AccountFriendlyName
sem magna nec quam.
tristique senectus et netus et malesuada fames ac
Phasellus elit
inceptos hymenaeos. Mauris ut quam vel sapien imperdiet ornare. In faucibus. Morbi
lacus, varius et, euismod et, commodo at, libero. Morbi accumsan
et, rutrum eu,
Donec nibh enim, gravida sit amet, dapibus id, blandit at, nisi. Cum
eu neque
est, mollis non, cursus non, egestas a, dui. Cras pellentesque. Sed
velit in aliquet lobortis, nisi nibh lacinia orci,
lorem. Donec elementum, lorem ut aliquam iaculis, lacus pede sagittis
in, tempus eu, ligula. Aenean euismod mauris eu elit. Nulla facilisi.
feugiat. Lorem ipsum dolor sit amet, consectetuer adipiscing
neque vitae semper egestas, urna justo faucibus lectus, a sollicitudin
Phasellus ornare. Fusce mollis. Duis sit amet diam eu dolor egestas
nisl arcu iaculis enim, sit
et pede. Nunc sed orci lobortis augue scelerisque mollis.
Curabitur consequat, lectus sit amet luctus vulputate, nisi sem semper
mi eleifend egestas. Sed pharetra, felis eget varius ultrices,
mauris
velit. Pellentesque
neque. In ornare sagittis felis. Donec tempor, est ac mattis semper, dui
eu erat semper rutrum. Fusce dolor quam, elementum
malesuada vel, venenatis vel, faucibus id, libero. Donec consectetuer mauris
odio tristique pharetra. Quisque ac libero
non, lacinia at, iaculis quis, pede. Praesent
ac mattis semper,
lectus pede et
nisl elementum purus, accumsan
erat
tincidunt,
Curabitur
ligula elit, pretium et, rutrum non, hendrerit id, ante.
ridiculus mus. Proin vel nisl. Quisque fringilla euismod
pellentesque, tellus sem mollis
libero lacus, varius et, euismod et,
nec urna et arcu imperdiet
id, mollis nec, cursus a, enim. Suspendisse aliquet,
nunc id enim. Curabitur massa.
eu erat semper rutrum. Fusce dolor quam, elementum at, egestas a,
egestas rhoncus. Proin nisl sem, consequat nec, mollis vitae, posuere at,
a, scelerisque sed, sapien. Nunc pulvinar arcu et pede. Nunc
non magna. Nam ligula
libero et tristique pellentesque,
dapibus ligula. Aliquam erat volutpat.
magnis dis parturient montes, nascetur ridiculus mus. Proin vel nisl.
urna et arcu imperdiet ullamcorper. Duis at lacus. Quisque
augue. Sed molestie. Sed id risus quis
id, mollis nec,
a, enim.
Nunc
pede ac urna. Ut tincidunt vehicula risus. Nulla eget metus
sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.
tincidunt adipiscing.
odio. Aliquam vulputate
eu, ultrices
Nunc ut erat. Sed nunc est, mollis non, cursus
nunc,
risus. Duis a mi fringilla
mi tempor lorem, eget mollis lectus pede
euismod et, commodo at, libero. Morbi accumsan laoreet ipsum.
Quisque porttitor eros nec tellus. Nunc lectus pede, ultrices a, auctor non,
lacus. Nulla tincidunt, neque vitae semper egestas,
consequat auctor, nunc nulla vulputate
Donec elementum, lorem ut aliquam iaculis, lacus pede sagittis
diam eu dolor egestas rhoncus. Proin nisl sem, consequat nec,
magna a tortor. Nunc commodo auctor velit. Aliquam nisl. Nulla
amet, consectetuer adipiscing
quis turpis vitae purus gravida sagittis. Duis gravida. Praesent eu nulla at
orci
sit amet luctus vulputate, nisi sem
amet ante. Vivamus non lorem vitae odio sagittis semper. Nam tempor diam
lacus. Quisque imperdiet, erat nonummy ultricies ornare, elit elit fermentum risus,
eu
vel
Donec est. Nunc ullamcorper, velit in aliquet lobortis, nisi nibh lacinia orci,
vitae odio sagittis semper. Nam tempor diam dictum
non, bibendum sed, est. Nunc laoreet lectus quis
mollis. Duis sit amet diam eu dolor
Duis at lacus. Quisque purus sapien, gravida non, sollicitudin a, malesuada
magna. Ut tincidunt orci quis lectus. Nullam suscipit,
nec enim. Nunc ut erat. Sed nunc est, mollis non, cursus non,
nec, mollis vitae, posuere at, velit. Cras lorem lorem,
Aenean gravida nunc sed pede. Cum sociis natoque penatibus et
magnis dis parturient montes,
molestie. Sed id risus quis diam luctus lobortis. Class
aliquam iaculis, lacus pede sagittis augue, eu tempor erat neque non quam.
Nam tempor diam dictum sapien. Aenean massa. Integer vitae nibh. Donec
magna. Duis dignissim tempor arcu. Vestibulum ut eros non enim commodo
nec, mollis
sem semper erat, in consectetuer ipsum nunc id enim. Curabitur
pellentesque eget, dictum placerat, augue. Sed molestie. Sed id
aliquam iaculis, lacus pede sagittis augue, eu tempor erat neque non
facilisis
semper cursus. Integer mollis. Integer tincidunt aliquam arcu. Aliquam ultrices
vel, faucibus id, libero. Donec consectetuer mauris
nunc sit amet metus. Aliquam erat volutpat.
Donec porttitor tellus non magna. Nam ligula elit, pretium et, rutrum non,
ornare. Fusce mollis. Duis sit amet diam eu dolor egestas rhoncus.
erat. Sed nunc est, mollis non, cursus non, egestas a,
sunny_talwar

I did not understand the if statement, but check

Table:

LOAD AccountFriendlyName

FROM

[https://community.qlik.com/thread/236104]

(html, codepage is 1252, embedded labels, table is @1);

FOR i = 1 to FieldValueCount('AccountFriendlyName')

  LET vNumOfSpaces = SubStringCount(FieldValue('AccountFriendlyName', $(i)), ' ');

  SET vLabelAccountName = '';

  SET j = 0;

  SET vLastPosition = 1;

  vLabel_AccountName$(i) = Null();

  FOR j = 1 to $(vNumOfSpaces)+1

  LET vIndex = Index(FieldValue('AccountFriendlyName', $(i)),' ',$(j));

  LET vLen = Len(FieldValue('AccountFriendlyName', $(i)))+1;

  LET vLabel_AccountName$(i) =  '$(vLabel_AccountName$(i))' & Trim(Mid(FieldValue('AccountFriendlyName', $(i)),$(vLastPosition),If($(vIndex) = 0, $(vLen), $(vIndex)) -$(vLastPosition))) & Chr(13);

  SET vLastPosition = $(vIndex);

  NEXT j;

NEXT i;

cbushey1
Creator III
Creator III
Author

Thank you for taking the time to work on this.

I think we are getting closer but still not exactly what I need.

The If statement was to determine if the length of the word was longer than 12 characters. I dont always want to break the sentence at each space. I want to ensure I can fit as much of the sentence on each line as possible before adding the line break. So if a sentence was "I can climb to the top of the hill", I want it to be stored as

"I can climb

to the top

of the hill"

The reason for the breaks where they are is because I am using "12" as my character limit per row. If that falls in the middle of a word then the line break should occur at the previous space.

*I also noticed that you have these being stored as variables for each value. Ideally, I would like this to just be a field within the same table that AccountFriendlyName comes from just with a different name.

marcus_sommer

I could imagine that a different approach to split your records and put them together again (with a certain logic) could be easier. Take a look on the following:

//RawData:

//LOAD AccountFriendlyName

//FROM [https://community.qlik.com/thread/236104] (html, codepage is 1252, embedded labels, table is @1);

//store RawData into RawData.qvd (qvd);

RawData:

Load

    trim(AccountFriendlyName) as AccountFriendlyName, SubStringCount(trim(AccountFriendlyName), ' ') as NumberOfSpaces,

    Len(trim(AccountFriendlyName)) as RecordLength, recno() as RecNo

From RawData.qvd (qvd);

let vNumberOfChars = 12;

SplitRecords:

Load

    *, ceil(WordLengthWithSpaceCum / $(vNumberOfChars)) as WordCluster,

    RecNo & '|' & ceil(WordLengthWithSpaceCum / $(vNumberOfChars)) as WordKey;

Load

    *, len(SingleWord) as WordLength, if(RecNo <> peek('RecNo'), len(SingleWord), len(SingleWord) + peek('WordLengthCum')) as WordLengthCum,

    if(RecNo <> peek('RecNo'), len(SingleWord), len(SingleWord) + peek('WordLengthCum') + IterNo - 1) as WordLengthWithSpaceCum;

Load

    RecNo, iterno() as IterNo, subfield(AccountFriendlyName, ' ', iterno()) as SingleWord

Resident RawData while iterno() <= NumberOfSpaces + 1;

ConcatWords:

load

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

resident SplitRecords group by WordKey;

- Marcus