Email Connector: Way to ignore #N/A fields in xls files ?

Options
SaskiaV
SaskiaV Member
in Connectors

I am using the email connector to send .xls files to DOMO. There were some fields in the excel file that contained "#N/A" and they were automatically converted to "ERROR 42" in the dataset that was created and caused my downstream dataflow to fail.

Is there a way to have the connector somehow ignore #N/A fields or convert them to NULL instead of "ERROR 42" ?

Appreciate any advise anybody can provide.

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @SaskiaV Add a formula tile to the beginning of your dataflow to only accept values that are not equal to ERROR 42 like this:

    case when `Field` <> 'ERROR 42' then `Field` end
  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    There is no way to ignore these rows in the connector itself, the best solution is to remove those rows in an ETL as @MichelleH suggests.

    If I solved your problem, please select "yes" above

  • SaskiaV
    SaskiaV Member
    Answer ✓

    Thanks for your suggestions. I didn't actually want to remove these rows, but solved it by having these fields replaced with NULL using following statement:

    case when Field like '%ERROR%' then NULL else Field end.

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @SaskiaV Add a formula tile to the beginning of your dataflow to only accept values that are not equal to ERROR 42 like this:

    case when `Field` <> 'ERROR 42' then `Field` end
  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    There is no way to ignore these rows in the connector itself, the best solution is to remove those rows in an ETL as @MichelleH suggests.

    If I solved your problem, please select "yes" above

  • SaskiaV
    SaskiaV Member
    Answer ✓

    Thanks for your suggestions. I didn't actually want to remove these rows, but solved it by having these fields replaced with NULL using following statement:

    case when Field like '%ERROR%' then NULL else Field end.

Categories