Using Subfield for Word cloud

[fah18]

Hi, 

I am trying to create a word cloud and a table by loading in the data via Excel. I have 2 columns called Negative and Positive as attached below.  I have multiple rows for these 2 columns. 

I used the word cloud chart and created 2 charts using 

=subfield(positive,' ') &  =subfield(negative,' ') --> Dimensions

=Count subfield(positive,' ')) & =Count subfield(negative,' ')) --> Measures 

The word cloud do appear to be how I want. But I created an additional table chart so that when we click a certain word, it will show up the whole sentence in the table. However, when a particular word is clicked, random sentences which do not contain the word is also shown. 

If i do not use the function subfield, the chart and table works as expected. but the Word cloud charts shows up in a sentence of 2/3 words.. 

I am stuck at this for ages. Please help out 😞

[Dalton_Ruer]

I would create a table for the Words in the load script so you aren't trying to calculate things in expressions that are repetitive

[fah18]

Hi @Dalton_Ruer ,

I am totally new to this. I did try loading it from the load script as a new table. It didnt work too.. showing the same results.. 😞

[Dalton_Ruer]

Here is a sample load script with a few sentiment rows. 

SentimentData:
Load * Inline [
Keyfield, Positive Words, Negative Words
1, 'Fun, Happy', 'Mean, Angrgy'
2, 'Laughing, Dancing', 'Sulking, Pouting, Crying'
3, 'Fun', 'Mean, Angry, Sad, Upset'
4, 'Happy, Laughing, Fun', 'Sad, Angry, Crying'
];

PositiveWords:
Load
Keyfield,
SubField("Positive Words", ', ') as PositiveWord
Resident SentimentData;

NegativeWords:
Load
Keyfield,
SubField("Negative Words", ', ') as NegativeWord
Resident SentimentData;

I then create a few tables in the UI to show the values. 

The WordClouds simply show the field PositiveWord or NegativeWord and the Measure is simply: Count(Keyfield):

If I select any word in the Word Cloud, or in the text list the Associative Model kicks in. Notice I selected the word FUN. Since that word is selected the associated Keyfield values are the only ones that are shown for the main data table. The table showing negative words only then shows the negative words that are associated with the Keyfields that are associated with the word Fun. 

If I continue the Associative walk and choose a Negative word like Angrgy, I get to see the other negative words that are "alternatives" because they exist for those keyfields associated with "Fun" and "Angry" (the right spelling 😀) and I see the excluded words that are not able to be chosen if honoring the current selections. 

 

[fah18]

Hi,

I tried and tested this. This is a great way to understand the concept. But the issue I have is I have multiple rows of data.. So how do I load it inline? 

But I found out an issue with the subfield function. It does not return all the words till end of the string. It only returns the first 2 words. What can be the issue here?

[Dalton_Ruer]

Not sure what you mean ... my example loaded the data using INLINE and it can be as many rows as you want or you can more reasonably use a database load for the columns. 

SUBFIELD should return all words, can you include an example of your data rows and the code you are using. 

[fah18]

I have used subfield(negative,' ') / subfield(positive,' ') for the word cloud chart. 

I have used negative /positive as dimensions at the table. If you see, it does not work as intended. As I select the word knowledge from the negative chart, it is also populating some random words that does not have the word knowledge. Likewise, the positive chart should not show up like this but should show the word corresponding to the positive columns. 

I would describe my problem similar to this as I experimented with a single row.  https://community.qlik.com/t5/New-to-Qlik-Sense/Problems-using-SubField-in-an-table-expression-in-Ex...

The subfield function do not return all the words in the string.