1

I have the following code in SQL that I need to modify to Tableau native logic, preferably without the use of initial sql or custom sql. Whereas the following code is limited to one time period I need to use multiple time periods. See the line with the 'O' in it. I do not want to use custom sql because I have an existing dashboard with a lot of automatic aliasing already in use. Please advise.

SELECT 
    case
        when T1.Source1 = 'nato' then T1.acc 
        when T1.MAST IN ('','note','lend') OR t1.MAST IS NULL then T1.ACC
        else T1.MAST
    end as ParNum,
        case
        when T1.Source1 = 'nato' then 'P'
        when T1.sys = 'ac' then 'P'
        when (
        case
        when T1.Source1 = 'nato' then T1.ACC
        when T1.MAST IN ('','note','lend') OR t1.MAST IS NULL then T1.ACC
        else T1.MAST
    end)            
        = T1.ACC then 'P'
        when (
        case
        when T1.Source1 = 'nato' then T1.ACC
        when T1.MAST ('','note','lend') OR t1.MAST IS NULL then T1.ACC 
        else T1.MAST
    end)
        not in (select PARSENAME(t1.ACC,2) from cocoabutter where mokey=202203) then 'O'
        else 'C'
    end as ParFlg, 
t1.*
FROM 
    cocoabutter t1
bert
  • 23
  • 3

1 Answers1

0

No need for Custom SQL

Just connect to the table cocoabutter and then define two calculated fields, ParNum and ParFlg (or give them longer names).

For example, ParNum could be defined as

   IF [Source1] = 'nato' OR ISNULL([MAST]) OR [MAST] IN ('','note','lend') THEN
      [ACC]
   ELSE
      [MAST]
   END
Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • And ParFlg? Note the line that says "not in" in the original code. – bert May 16 '23 at 18:25
  • I did the first field as an example , figured you could then use the same approach to create calculated field for the second one – Alex Blakemore May 17 '23 at 19:33
  • The second part is a little different. I need to make sure I only code 'O' if the ParNum is not found in the set consisting of the second part of the ACC field but only for the same mokey as the record you are currently evaluating. In the example above, I am using March 2022 only but this need to work for any month. – bert May 18 '23 at 15:31
  • Honestly, your logic for that second field seems really confused and self contradictory. I’d try hard to simplify regardless of whether you express it in SQL or Tableau’s calculation language. Its hard for an outsider to do that for you, but try making table of the logical outcomes and combining rows that lead to the same outcome – Alex Blakemore May 19 '23 at 16:10