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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • 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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hi @GrantSmith,

    Thank you for the response!

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



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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.


    β€œThere is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • @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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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.

    Jae Wilson
    Check out my πŸŽ₯ Domo Training YouTube Channel πŸ‘¨β€πŸ’»

    **Say "Thanks" by clicking the ❀️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • @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
    GrantSmith Coach
    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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 ☺️

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