A report developer (a non-IT business user) came to me regarding an issue in a workbook of theirs. The reason isn't relevant because there was something else in the workbook that challenged me. I saw that it used Custom SQL-- something I heavily frown upon! -- and with a JOIN filter that took some pondering to get right. Please note that I have modified the SQL for brevity as it's the concepts we are concerned with. The data source is an IBM DB2 database.
Here's the first part that furled my brow, highlighted in orange:
,'XXXXXXXX' as SUPLR_NAME
,'######' as CURR_HTS
END AS HTS_CHGD
,Trim(T1.ITEM_DESC) as DESCRIPTION
,trim(T3.BYR_CD) ||'-'|| trim(T3.BYR_NAME) as BUYER
Why the bulk comment (/*...*/)? If it's not needed then don't include it as it can be added later if necessary Get rid of it!
'XXXXXXXX' as SUPLR_NAME served no purpose, it wasn't used. Get rid of it!
Same for '######' as CURR_HTS , get rid of it!
The CASE statement can be made into a calculated field. Get rid of it!
TRIMming the description can be done in a calculated field or where needed. Get rid of it!
The BUYER field can be a calculated field, too. Get rid of it!
We need to remember that if we are not going to use Custom SQL then we need to convert it into such a form that doesn't require functions in the SELECT body.
Next came the FROM body:
LEFT OUTER JOIN CUSTOMS.ITEM_MASTER T1
left outer join customs.facilities_dim_v v2
left outer join customs.supplier_parent t2
left outer join customs.item_loc_vendor_dim_v v3
left outer join customs.purchasing_buyer t3
left outer join customs.udi_itc_mdl_xref t4
Again with the comment! Get rid of it!
Notice the blue line, a condition of the JOIN, and the magenta line, a condition for the entire output.
What I did was add the necessary tables to the Tableau data source canvas and created the basic JOINs. This got the information into Tableau so I could then work with the conditionals. Here's what it looks like and this shows the ITEM_LOC_VENDOR_DIM_V table (technically a view but just roll with me on this, okay?) that will be LEFT JOINed to the RVC_TOOL_NAFTA_V table with that blue condition:
Taking care of the WHERE condition
Next, doing the easy part first, I created a data source Filter:
Thus the overall WHERE condition, v1.make_buy_cd='B', is now taken care of
Taking care of the JOIN condition, starting on the right side
Now the conditional part (blue text) for this LEFT JOIN:
...left outer join customs.item_loc_vendor_dim_v v3
left outer join customs.purchasing_buyer t3...
This was a real head-scratcher for me because I'm not familiar with creating 'custom' Join Clauses on Tableau's data source canvas, thus it took some experimenting before I got the correct results. Here's the final solution:
See the last line? Well, it turns out both the left and right side MUST be filled out otherwise the custom JOIN clause won't "stick", it will disappear. If you don't see it then it's not there. So, how do we get that? It's very easy, just click the drop-down arrow in the last row of the Join box and select the option "Create Join Calculation..."
You'll be presented with the calculated field editor where you can create your Join Condition just as you would create a Calculated Field:
When done simply click the green OK button and it will be added. You must have a Join Clause for the other side of the JOIN as well. After BOTH sides of the Join Clause have been created you can then exit the Join box.
I used parenthesis simply to make sure the order of operations happens as I expect it to but they are not required.
Setting the Join Condition on the left side
Both sides of the Join Condition need an entry, a value, since there's that equal ( = ) operator between them. Since I don't care about what's on the left side of the JOIN I just make sure it is always a Boolean TRUE. This just fulfills the need to have something there. If TRUE is not valid for your situation simply use (1=1). Conversely, if you need a FALSE condition you can alternately use (1=0).
Now, on the other side of the equal condition is where I need to make sure only rows with the letter "P" for the [Prm Alt Vndr Cd] are pulled. The result of this needs to be Boolean (TRUE or FALSE). The equation for the JOIN condition is now completely set.
How Tableau translates it
Here is what the query looks like when I select Data >> Convert to Custom SQL. This is only for the curious and isn't necessary to do when converting Custom SQL to the data source canvas. I'm only focusing on the FROM body since the SELECT body is now no longer "interesting" to look at:
FROM "CUSTOMS"."RVC_TOOL_NAFTA_V" "RVC_TOOL_NAFTA_V"
LEFT JOIN "CUSTOMS"."ITEM_MASTER" "ITEM_MASTER" ON
("RVC_TOOL_NAFTA_V"."CHILD_ITEM_NO" = "ITEM_MASTER"."ITEM_NO") AND ("RVC_TOOL_NAFTA_V"."CO_CD" = "ITEM_MASTER"."CO_CD") AND ("RVC_TOOL_NAFTA_V"."ORG_CD" = "ITEM_MASTER"."ORG_CD")
LEFT JOIN "CUSTOMS"."FACILITIES_DIM_V" "FACILITIES_DIM_V" ON ("RVC_TOOL_NAFTA_V"."PLNT_CD" = "FACILITIES_DIM_V"."PLANT_CD")
LEFT JOIN "CUSTOMS"."SUPPLIER_PARENT" "SUPPLIER_PARENT" ON ("RVC_TOOL_NAFTA_V"."SUPLR_CD" = "SUPPLIER_PARENT"."SUPLR_CD")
SELECT "ITEM_LOC_VENDOR_DIM_V"."CO_CD" AS "CO_CD",
"ITEM_LOC_VENDOR_DIM_V"."ORG_CD" AS "ORG_CD",
"ITEM_LOC_VENDOR_DIM_V"."PLNT_CD" AS "PLNT_CD",
"ITEM_LOC_VENDOR_DIM_V"."PPF_BYR_CD" AS "PPF_BYR_CD",
"ITEM_LOC_VENDOR_DIM_V"."ITEM_NO" AS "ITEM_NO",
"ITEM_LOC_VENDOR_DIM_V"."SUPLR_CD" AS "SUPLR_CD",
"ITEM_LOC_VENDOR_DIM_V"."PRIM_ALT_VNDR_CD" AS "PRIM_ALT_VNDR_CD",
"ITEM_LOC_VENDOR_DIM_V"."CURR_TS" AS "CURR_TS",
(CASE WHEN ("ITEM_LOC_VENDOR_DIM_V"."PRIM_ALT_VNDR_CD" = 'P') THEN 1 WHEN NOT ("ITEM_LOC_VENDOR_DIM_V"."PRIM_ALT_VNDR_CD" = 'P') THEN 0 ELSE NULL END) AS "$temp0"
FROM "CUSTOMS"."ITEM_LOC_VENDOR_DIM_V" "ITEM_LOC_VENDOR_DIM_V"
) "t0" ON
("RVC_TOOL_NAFTA_V"."CO_CD" = "t0"."CO_CD")
AND ("RVC_TOOL_NAFTA_V"."ORG_CD" = "t0"."ORG_CD")
AND ("RVC_TOOL_NAFTA_V"."PLNT_CD" = "t0"."PLNT_CD")
AND ("RVC_TOOL_NAFTA_V"."SUPLR_CD" = "t0"."SUPLR_CD")
AND ("RVC_TOOL_NAFTA_V"."CHILD_ITEM_NO" = "t0"."ITEM_NO")
AND ((CASE WHEN (1=1) THEN 1 WHEN NOT (1=1) THEN 0 ELSE NULL END) = "t0"."$temp0")
LEFT JOIN "CUSTOMS"."PURCHASING_BUYER" "PURCHASING_BUYER" ON
("t0"."PPF_BYR_CD" = "PURCHASING_BUYER"."BYR_CD")
AND ("t0"."SUPLR_CD" = "PURCHASING_BUYER"."SUPLR_CD")
See where $temp0 is created in the sub-SELECT body? That is the right side of the Join Condition I created on the data source canvas. The result set of that sub-SELECT is given the alias "t0".
Inside the ON body of the LEFT JOIN is the right side of the Join Condition I created on the data source canvas. This is where the TRUE = [Prm Alt Vndr Cd]= 'P' Join Condition I set up on the data source canvas comes into play. What is interesting about the translation is that Tableau converted TRUE to (1=1).
Even with the poor Custom SQL query, the extract created by this data source took slightly less time than using a proper Tableau data source canvas one. I'm no expert by any means but my guess is that the use of Tableau-generated CASE statements in the JOINs doesn't help. The fact that both sides of a Join Calculation are necessary is likely the culprit. It would be nice to have the ability to have one-sided Join Calculations, thus more closely mimic what is written in the SQL query.