topic Re: Loop for Field name in QlikView

Loop for Field name

cbushey1 — Wed, 12 Oct 2016 15:07:51 GMT

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;

Re: Loop for Field name

sunny_talwar — Wed, 12 Oct 2016 16:26:20 GMT

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

Re: Loop for Field name

cbushey1 — Wed, 12 Oct 2016 17:13:08 GMT

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.

Re: Loop for Field name

sunny_talwar — Wed, 12 Oct 2016 17:15:44 GMT

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

Re: Loop for Field name

cbushey1 — Wed, 12 Oct 2016 17:42:39 GMT

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.

Re: Loop for Field name

sunny_talwar — Wed, 12 Oct 2016 17:48:04 GMT

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

Re: Loop for Field name

cbushey1 — Wed, 12 Oct 2016 19:32:05 GMT

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,

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,

Re: Loop for Field name

sunny_talwar — Wed, 12 Oct 2016 20:50:56 GMT

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;

Re: Loop for Field name

cbushey1 — Fri, 14 Oct 2016 18:59:43 GMT

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.

Re: Loop for Field name

marcus_sommer — Sat, 15 Oct 2016 13:13:57 GMT

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

Re: Loop for Field name

cbushey1 — Mon, 17 Oct 2016 14:45:45 GMT

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?

Re: Loop for Field name

sunny_talwar — Mon, 17 Oct 2016 15:03:00 GMT

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/StringAggregationFunctions/concat.htm

Re: Loop for Field name

marcus_sommer — Mon, 17 Oct 2016 15:05:20 GMT

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

Re: Loop for Field name

cbushey1 — Mon, 17 Oct 2016 16:04:30 GMT

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?

Re: Loop for Field name

marcus_sommer — Tue, 18 Oct 2016 11:31:40 GMT

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

Re: Loop for Field name

cbushey1 — Tue, 18 Oct 2016 12:46:18 GMT

Here is a sample QVW.

Re: Loop for Field name

marcus_sommer — Tue, 18 Oct 2016 17:13:54 GMT

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

Re: Loop for Field name

cbushey1 — Tue, 18 Oct 2016 18:02:12 GMT

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!

Re: Loop for Field name

cbushey1 — Tue, 18 Oct 2016 18:03:19 GMT

Thank you Marcus again for all your help on this!