Creating a Word Cloud chart from columns with phrases

melyeo · 2021-08-08T22:36:28+00:00

There was an error rendering this rich post.

Hi, I'm new to DOMO so would appreciate some advice on the following issue.

I'm trying to create a Word Cloud chart from columns with phrases. I followed instructions from Word Cloud Chart – Domo but when i get to step 7:

Copy and paste the content from the following SQL file into the new transform: call transform_2.sql

I get an error "Unknown column 'hazard' in 'field list'. My input dataset is 'Hazard' with column 'title'

CALL word_cloud(`hazard`, '`title`' , 'y','y' );

I have tried various permutations of the fields in the CALL function but it is still invalid.

Any help on this is greatly appreciated.

Thanks,

Mel

Find more posts tagged with

Accepted answers

GrantSmith

Hi @melyeo

I was playing around with this today and noticed that there's a bug with Domo's code where it's expecting you to provide additional columns besides just the single column. To get around this issue I just created a new transform where I added a new static string column:

SELECT `value`, 'HACK' as v2
FROM ...

Then in the final output dataset I just selected the columns I needed and ignored my hack column.

SELECT `value`
FROM `final`

This appeared to solve the problem for me.

All comments

GrantSmith

Hi @melyeo

look at your input dataset at the top. On the bottom of the box it’ll show the name of the table you can use. It’ll be in a grey box

melyeo

Hi @GrantSmith,

Thank you for the response!

The table is hazard as per below screenshot with the CALL function - unsure how to proceed next.

MarkSnodgrass

@melyeo I followed the steps in my instance and initially got the error that you did. I figured out it is because you used ticks around the table name and it wants single quotes. I'm guessing the autocomplete when you started typing the table name put the ticks in there. Your statement should look like this:


CALL word_cloud('hazard', '`title`', 'y','y' );

The table name parameter should have single quotes only and not ticks. The column name parameter should have both ticks and single quotes.

melyeo

Hi @MarkSnodgrass, unfortunately I still got an Invalid run :(

ST_Superman

@melyeo - make sure that you run all of the step in the SQL dataflow prior to calling the procedure.

i.e. make sure that you run the procedure_transform_1 step first and then run the step that calls the procedure. (you can also select the down arrow next to the "RUN SQL" box in your screenshot and select run to this point.

This is a longshot, but since it looks like your syntax is correct, that is all I can suggest.

GrantSmith

@melyeo - Have you tried removing the all of your code (comments and all) and just manually typing in the function call manually in case there was a hidden character copied into your transform?

jaeW_at_Onyx

@melyeo are you confident that the 'title' column never contains NULLs or blanks? I suspect Domo's code may not have good error handling built into it. So make sure your table, hazard, does not include rows where title is not populated.

https://www.youtube.com/watch?v=oYcpYE7DiV4&t=4s

this video walks you through how you could calculate this from scratch step at a time.

in a nutshell for each row of hazard, you'd want to count the number of spaces. then duplicate that row, n number of spaces. then keep the text after the nth space.

in the world cloud documentation, it says you can't do this transform in MagicETL. as of Magic 2.0 that's actually no longer true. You can recreate this pipeline in Magic 2.0 and it will be much more performant (faster) across larger datasets than this MySQL dataflow you're trying to run.

melyeo

@ST_-Superman-_ and @GrantSmith - I have run the steps prior to calling the procedure and removed the comments but unfortunately no success.

@jaeW_at_Onyx - checked and no NULLs or blanks in the column. I will look at your video and see whether I can get more insights.

Thanks all.

GrantSmith

Hi @melyeo

SELECT `value`, 'HACK' as v2
FROM ...

Then in the final output dataset I just selected the columns I needed and ignored my hack column.

SELECT `value`
FROM `final`

This appeared to solve the problem for me.

melyeo

Thanks heaps @GrantSmith it solved the problem!!

I was trying to troubleshoot with a simple table with 1 column, not knowing that this would create an issue. Thanks for pointing this out ☺️

isylaman

Hey,

I totally feel you, Mel... Starting with DOMO can be a bit tricky, but don't worry, we've all been there. It seems like you're on the right track, but the error you encountered can be really frustrating. Your perseverance is admirable!

So, about that error: I faced a similar situation when I was working on a Word Cloud chart. It looked something like this: https://andersenlab.com/services/cloud-development-services/cloud-migration-services-and-consulting.

The issue might be that the code expects more columns than you're providing. What I did to work around it was to create a new "dummy" column like this:

SELECT title, 'PLACEHOLDER' as dummy_column FROM your_table

Then, when you're making your actual chart, you can just select the columns you need, excluding the dummy one.

SELECT title FROM your_final_table

It's a bit of a workaround, but it did the trick for me. DOMO's quirks can sometimes trip us up, but don't give up! You're making great progress. Keep experimenting, and you'll become a DOMO pro in no time. 😏