HELP! Can not get this ETL Formula to Validate

jtrollinger
jtrollinger Member
in Magic ETL

Can not get this ETL Formula to Validate

CASE WHEN HdrParentItemCode IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN'
ELSE
CASE WHEN HdrParentItemCode IN ('ZTOPOUT', 'ZGAS') THEN 'Topout'
ELSE
CASE WHEN HdrParentItemCode IN ('ZTRIM','ZDROPIN') THEN 'Trim'
ELSE
CASE WHEN HdrParentItemCode IN ('ZPUNCH') THEN 'Punch'
ELSE
'Others'
END

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Only the first CASE keyword is needed you can remove the others but leave them when and clauses

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

    @jtrollinger In addition to Grant's feedback, you only need one ELSE to designate 'Others'

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Good clarification @MichelleH

    @jtrollinger it should end up with something like this

    CASE WHEN `HdrParentItemCode` IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN'

    WHEN `HdrParentItemCode` IN ('ZTOPOUT', 'ZGAS') THEN 'Topout'

    WHEN `HdrParentItemCode` IN ('ZTRIM','ZDROPIN') THEN 'Trim'

    WHEN `HdrParentItemCode` IN ('ZPUNCH') THEN 'Punch'

    ELSE

    'Others'

    END

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Only the first CASE keyword is needed you can remove the others but leave them when and clauses

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

    Thanks Grant. Syntax is everything.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @jtrollinger In addition to Grant's feedback, you only need one ELSE to designate 'Others'

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Good clarification @MichelleH

    @jtrollinger it should end up with something like this

    CASE WHEN `HdrParentItemCode` IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN'

    WHEN `HdrParentItemCode` IN ('ZTOPOUT', 'ZGAS') THEN 'Topout'

    WHEN `HdrParentItemCode` IN ('ZTRIM','ZDROPIN') THEN 'Trim'

    WHEN `HdrParentItemCode` IN ('ZPUNCH') THEN 'Punch'

    ELSE

    'Others'

    END

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

Categories