Help with Formula/ Beast mode to concat values based on unique key

ozarkram
ozarkram Member
in Magic ETL

Hi All,

I am trying to write a beast mode formula from the input table below:

image.png

to the output table

image.png

Basically I am trying to group by pickno and then group the code R into the column Reason and the code S into the column shipinstruction (combining the names and the reason or shipping instruction)..

Can you please help me here..I am attaching the excel sheet for your reference..

Sample sheet.xlsx


Tagged:

Best Answer

  • mhouston
    mhouston Contributor
    Answer ✓

    I think you just have a syntax error. You should not have "as reason" in your case statement. The syntax for case in Domo is:

    CASE

    WHEN .... THEN

    ELSE

    END

    Otherwise, as long as your column names are valid, the formula should work (although you may want to add a space before 'reason' so that you have a space in the final value).

Answers

  • ozarkram
    ozarkram Member

    Hi All, Any help on this would be appreciated!

  • mhouston
    mhouston Contributor

    Is there a reason you need to do this in a beast mode? This would be easier to do in an ETL - you could use a formula tile to concatenate the names and reason and then the names and ship instruction, and then do a group by tile to group on pickno.

  • GrantSmith
    GrantSmith Coach

    You're not able to do this in a beast mode. You can use a GROUP BY tile in Magic ETL and tell it to separate the values by commas as your aggregation method.

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

    I am getting an error when I try to do the following formula


    CASE WHEN `REASONORINSTRUCTIONCODE`='R' THEN CONCAT('name-',`USERWHOENTEREDTHISLINE`,'reason-',`REASONSORSHIPPINGINSTRUCTIONS`) ELSE '' END as reason

    Can you please help here..

  • mhouston
    mhouston Contributor
    Answer ✓

    I think you just have a syntax error. You should not have "as reason" in your case statement. The syntax for case in Domo is:

    CASE

    WHEN .... THEN

    ELSE

    END

    Otherwise, as long as your column names are valid, the formula should work (although you may want to add a space before 'reason' so that you have a space in the final value).

  • ozarkram
    ozarkram Member

    @mhouston :Thanks a lot for your response!

Categories