Hi,

I am currently extracting data from SQL database using a custom SQL query which has a nested query in it. Now as we know its always better better to avoid custom sql in tableau from performance point of view, I was wondering how to develop the data model as I have in my custom SQL on the data canvas of Tableau. I can easily put the joins and all, the only issue arises when I have to configure the nested query which has a calculated field in it.

Below is a sample query I used in my custom sql:

SELECT x.[PreOrderCode]
      ,x.[BrandName]
      ,x.[CreationTime]
      ,x.[LastUpdateTime]
      ,r.CountryName as Country
from


(SELECT po.[PreOrderCode]
      ,b.[BrandName]
      ,po.[CreationTime]
      ,po.[LastUpdateTime]
   , (case 
      when po.BrandId in ('1')  and po.PreOrderStatusId in ('0','1')
               then 
     (SUBSTRING(po.PreOrderDetails,
  CHARINDEX('ShippingCountryIso":"', po.PreOrderDetails) + LEN('ShippingCountryIso":"'),2)) COLLATE DATABASE_DEFAULT

  end) as BillingCountry


  FROM [PreOrder] po
   LEFT JOIN Orders o
   ON po.OrderNumber = o.BrandOrderReference
   LEFT JOIN NameAddresses n
   ON o.ConsigneePartyId = n.NameAddressId
   LEFT JOIN  Payments p
   ON o.PaymentId = p.PaymentId
   LEFT JOIN Countries c
   ON n.CountryIso = c.Iso
   LEFT JOIN  Brands b
   ON b.BrandId = po.BrandId


    where  
  po.[CreationTime] > '2016-03-31 23:59:59' and 
  po.PreOrderStatusId in ('0','1'))x
   
   LEFT JOIN [Countries] r
                  ON  x.BillingCountry = r.Iso

When I tried to configure the CASE statement in the nested query, I couldn't find a way for it. I have prepared an equivalent Tableau Formula for it, but have no idea how to use it efficiently. I tried creating a calculated field with below formula and putting it on filter shelf. But I think that will only decrease the performance. I am interested in Extract filters rather than normal/context filters.

Tableau Equivalent Formula for CASE statement in above SQL:

IF (([Brand Id] = 1)  AND  ([Pre Order Status Id] = 0 OR [Pre Order Status Id] = 1))
THEN
(MID([Pre Order Details],(FIND( [Pre Order Details],'ShippingCountryIso":"') + LEN('ShippingCountryIso":"')),2))
END

Any suggestion is highly appreciated.

Thanks,

M