Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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!
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
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.
If you can provide a sample of data, we can play around with it to make it work.
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.
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
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, |
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;
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.
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