LISTAGG or equivalent during data import from NetSuite

mberkeley
mberkeley Contributor
edited March 2023 in SQL DataFlows

What SQL should I be using during an import to collapse data from a column into ',' delimited strings?

when I use:

listagg(item.NAME,',') "ITEM(S)", 

I get the error:

[NetSuite][SuiteAnalytics Connect JDBC Driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# l27jpdhw1857xj1xsmvi0[400]

with just item.NAME "ITEM(S)", I get multiple rows per 'user' each row with a single item.

I've tried group_concat() also.

Please help.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    Those functions are aggregates, are you grouping your data on your other columns?

    Have you tried just utilizing a MagicETL dataflow after import and using the group by tile and selecting to combine values with commas?

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

    This is the only data element that has multiple rows in the data everything else is 1 row per 'user'. I was hoping to be able to do this during the import process. It's a pretty standard SQL function.

    If I can't make it work during import, the next step is to aggregate after import. It looks I can do it in the 'Add Formula' or 'Group By' Magic Tiles.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @mberkeley the SQL driver is built by Netsuite.

    Netsuite would have to build that functionality into the driver before Domo's connector would support it.

    Also purely from a best practices point of view it might make sense to import the data as is. yes it does lead to higher data volumes, but in Domo for filtering purposes having the values spread across rows enables the use of filter cards. (multi select "find all rows with permutations that contain foo" isn't natively supported without a lot of user clicking.

    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"

Categories