Creating a Word Cloud chart from columns with phrases

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

Best Answer

  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted Answer

    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.

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    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
    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
    MarkSnodgrass Portland, Oregon 🟤

    @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
    melyeo ⚪️

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


  • @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
    GrantSmith Indiana 🔴

    @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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @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.


    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
    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.


  • 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 ☺️