Tableau Desktop Coding Bootcamp | Logical Functions 2
Summary
TLDRIn this video, Bawar walks through key concepts in Tableau, focusing on logical functions, handling null values, and writing calculations. He explains how to categorize data using IF and CASE statements, manage errors in real-time reports, and handle common issues like division by zero. The video also covers important techniques like using ZN and ISNULL functions to standardize data and avoid errors in Tableau calculations. Bawar encourages viewers to practice the discussed methods and invites them to explore the logic further by writing nested IF statements.
Takeaways
- 😀 The video is a part two session on coding and explores further Tableau calculations and logic building.
- 🖥️ The presenter demonstrates how to categorize data in Tableau using logical functions, such as categorizing based on profit values.
- 🚨 Exceptional handling is explained, focusing on how to deal with null values in real-time scenarios.
- 📝 The 'if null' and 'is null' functions are discussed as important tools to handle missing or null values in Tableau.
- 📅 A case statement is used to convert month numbers into month names, a common requirement in real-time scenarios.
- 🤔 The presenter suggests using multiple methods, like nested ifs, to achieve the same result for better understanding.
- 📊 Another use case is presented where subcategories are grouped into categories using a case statement.
- 🔢 The concept of handling division by zero errors in calculations is covered using an if-else condition to avoid null values.
- 📉 The ZN function is introduced as a way to replace null values with zero, a crucial function for standardizing Tableau code.
- ⚙️ The importance of functions like ABS (absolute value) to ensure code stability and handle specific requirements in calculations is discussed.
Q & A
What is the primary topic of the video?
-The video is focused on coding logic development in Tableau, including handling null values, writing logical functions, and using conditional statements like 'IF', 'IS NULL', and 'CASE' for data categorization and calculations.
Why is handling null values important in real-time projects?
-Handling null values is crucial because in real-time data, missing or undefined values can cause issues in calculations and reporting. By addressing null values, you can ensure your logic works smoothly, even when data is incomplete or unexpected.
What function does the speaker use to handle null values in Tableau?
-The speaker uses the 'IFNULL' function to handle null values in Tableau, replacing nulls with a default value, such as 'Not Defined' or zero.
What is the purpose of the 'IS NULL' function mentioned in the video?
-The 'IS NULL' function is used to check whether a given value is null or not. It returns a Boolean result: 'TRUE' if the value is null and 'FALSE' if the value is not null.
How does the speaker categorize profit values using conditional logic?
-The speaker categorizes profit values using a conditional 'IF' statement. For example, if the sum of profit is greater than 40,000, the profit is categorized as 'Good Profit.'
What common interview question related to Tableau is discussed in the video?
-A common interview question discussed in the video is how to write a case statement to categorize months or values in Tableau. This question checks the interviewee's logical ability to work with data in different formats.
How can you categorize months in Tableau based on numbers using a CASE statement?
-To categorize months based on numbers using a CASE statement, you can map the numeric values (e.g., 1 for January, 2 for February) to their respective month names, allowing you to display month names instead of numbers in your Tableau visualizations.
What is the purpose of using a 'CASE' statement in the video?
-The 'CASE' statement is used to categorize data into different groups based on conditions. For instance, the speaker uses it to categorize subcategories into different groups, like 'Group 1', 'Group 2', etc., based on subcategory values.
How does Tableau handle division by zero in calculations?
-In the video, the speaker demonstrates handling division by zero by using a conditional 'IF' statement. If the denominator is zero, Tableau returns zero to avoid errors in calculations.
What function does the speaker recommend using to standardize code and handle null values?
-The speaker recommends using the 'ZN' function in Tableau to standardize code. The 'ZN' function replaces null values with zero, ensuring that calculations can proceed without interruptions due to nulls.
Outlines
👋 Introduction and Recap of Part 1
The video begins with a greeting from the host, Al Bawar, and an introduction to his YouTube channel, Express Your Data. He briefly recaps the first part of the series, where viewers learned how to build logic and categorize data in Tableau. Al encourages new viewers to watch the first video and subscribe before continuing. He outlines that this video will cover advanced calculations in Tableau, focusing on building on previous lessons, handling data categorization, and writing more complex functions.
💡 Categorizing Profit Data in Tableau
In this section, the host introduces a logic-based calculation to categorize profits. He demonstrates how to assign values like 'Good Profit' if the profit is above a certain threshold (e.g., $40,000). He addresses handling null values that result from incomplete logic, introducing the concept of exceptional handling in real-time scenarios. Al explains the use of the `IFNULL` function to replace nulls with meaningful data like 'Not Defined' and compares this with the `ISNULL` function, which checks whether a value is null, returning a Boolean result.
🗓️ Using Case Statements to Display Month Names
This paragraph focuses on using a `CASE` statement to convert month numbers into readable month names. Al walks through the process of building this logic in Tableau, replacing month numbers with their corresponding names (e.g., 1 = 'Jan'). He highlights the importance of the `CASE` statement for data transformation and mentions how such tasks are often encountered in technical interviews. He wraps up by showing how to handle errors in logic, particularly when there is a mismatch in months, and invites viewers to practice writing similar logic using nested IF statements.
📊 Grouping Subcategories Using Case Statements
Al demonstrates how to group subcategories into different zones using a `CASE` statement. He groups subcategories (like Accessories, Appliances, etc.) into multiple groups, explaining how Tableau reads these subcategories and how to assign them into custom groups. This is a common task in real-world scenarios where users need to categorize data into specific zones or headers. The use case showcases how to logically organize and group data in Tableau through a step-by-step example.
🧮 Handling Division by Zero in Tableau
In this section, Al explains the challenge of handling division by zero errors in Tableau. He demonstrates how to avoid such errors by checking if the denominator is zero before performing any division. If the denominator is zero, Tableau should return 0; otherwise, it calculates the profit by dividing profit by sales. This is a crucial technique for real-time data handling where division by zero can cause errors or misleading results. Al also introduces the `ZN` (Zero Null) function to replace null values with zeros and standardize calculations.
Mindmap
Keywords
💡Tableau
💡If-Null Function
💡Case Statement
💡Subcategory
💡Profit
💡Null Values
💡Logical Functions
💡Is-Null Function
💡String Values
💡ZN Function
Highlights
Introduction to categorizing data and writing calculations in Tableau.
Explanation of how to use logical functions to handle data.
Tutorial on using 'if' statements to categorize data based on profit thresholds.
Introduction to handling null values with the 'ifnull' function.
Explanation of handling exceptional cases in data with null values.
Introduction to 'isnull' for determining whether a value is null in Tableau.
Use of 'case' statements to convert numerical month values into month names.
Demonstration of categorizing data into groups using 'case' statements.
How to handle division by zero in calculations.
Demonstration of using the ZN function to replace null values with zeros in calculations.
Introduction to standardizing code with the ZN function for real-time calculations.
Example of using Tableau's built-in functions to compute quick table calculations.
Explanation of using the absolute (ABS) function in Tableau for handling negative values.
Encouragement to write logic in multiple ways for better understanding and flexibility.
Concluding remarks on the importance of mastering different ways to handle data in Tableau for real-time application.
Transcripts
hello everyone this is all bawar and
welcome to my YouTube channel express
your data so we were you know learning a
lot in our coding sessions and this is
part two video where we will explore
what all we have learned till now and
we'll continue so if you are new to my
channel don't forget to like share and
subscribe and let us get started without
wasting much time
so if you have friends in my last video
video please do
watch that video so in that video we
have learned how to build logic from the
base and we have seen how the data is
getting categorized in Tableau and how
the execution you know is happening step
by step so I'm going to take it a
further level up and we are going to
write few more calculations and we'll
see how Tableau is trying to compute
that okay so for that again what I'm
doing is I'm taking my sub category and
I'm also taking my profit
for now because we have got all the
values in that and I'm just sorting it
now let us open one of the previously
built logic because I want to take some
part from that and uh okay
to
okay never mind we'll we'll write it so
I'm writing logical
functions
so it's day two for us okay now what I'm
doing is like we have categorized
earlier using the same I'm trying to
categorize uh it for some time uh for
some values on this so if
my sum of profit
okay is greater than uh let's say assume
I'm giving 40 000 okay then I want to
categorize as good profit
okay and without specifying end part or
else part I'm just ending my statement
here okay so now like we know it will
generate uh or create null values for us
right see here till here it is like this
now if at all you want to handle this
null so we call it as exceptional
handing
X
exceptional
handling how do you handle the null
values that are coming in your data one
of the very important you know uh
task in real time because most often
after writing your entire logic only for
some part of data we get this kind of
things okay so we need to think about
that also okay so now
we have categorized this as good profit
but we never expected that you know we
will have profits less than or in this
range now later on after moving your
report into production you got some
values which are not falling under your
condition then in that case what can we
do
so now
I want to handle this so I'm writing
simply
if
null
now observe here I am passing this
conditional statement to this if
statement so if my value is returning
null then specify as not defined
and I am simply closing it now what is
now what will happen first this inner
statement will get executed it will it
will print whatever the value is there
okay so in cases where it is null then
it will print as not not defined click
on apply
see now clearly if this kind of value is
uh we are getting in the calculation
then we'll know that okay this is coming
from this part and we need to fix this
okay so that is your if statement so
many times we use this if null in real
time as well now this is because string
I am deriving a string or defining a
string value here
now suppose you know instead of this
string value I want to uh return 0 in
cases of that so that is also one
possible case where you might get nulls
in number
okay so that will see uh and uh other
statement with respect to if null we
also have another statement which we can
use
is null so is null will return either
this statement is returning a null value
or not that's it okay so for this we
don't need
another condition so just what I'm doing
is uh
just
removing this
and removing or just keeping it as is
null
now e is null of our
original if statement that's it
now what will happen this is this will
become Boolean statement just it will
return either true or false so observe
now
if I see here false this means for these
three it is not it is giving us some
value so it is not null false means it
is not null
okay otherwise
it is
true which means for all other values
from papers it is returning
null value that is what it is trying to
tell us from this statement
okay so that is your if null and E is
null statement one of the very you know
important activity
of handling this now what we'll do is
we'll write a simple case statement okay
so if you remember in our previous video
we have uh derived some
values related to month same thing I'm
trying to do here and uh
let us keep this as is and uh
I will see if date related is there or
not okay so we have date name here I'll
just remove this and this
and I'll add it here
so I have assumed in your database you
are getting your months as this one two
three four till 12. now you got a
requirement that you know you have to
display month name
and not the number okay while there are
multiple ways of doing that I am just
doing it using a case statement just so
that we can understand how the statement
will work so now I'm just calling case
of
this date and name
so when I call that Tableau will try to
read all the values that are there in
this
okay now when
then
I want to print as
like say Jan
and I am and date
okay so this is valid now okay so
likewise I am writing it for all the
months here
two three four five six
okay I'll simply copy this
and paste it
okay let us uh
so I'm gonna write the numbers here to
three
four
five six so one common use case that we
might get in real time so you should be
aware of this logic as well it's
imported for us to understand so
likewise now I can write
my month name now I can use it wherever
I want so first of all I want to
use in number format or
string format whatever I want I can use
it
so July
so often this question is also asked in
interview many times I think in general
today I've asked I think in uh
Accenture also they have asked I think
in Amazon also they asked this kind of
you know question in preliminary around
so again the question is just to check
your logical ability okay so not in
terms of Tableau but technologically you
should be in a state that
you can derive this
okay I'm clicking on apply and I can
take this statement and I can drop it
now you see it is returning me
this value okay so now you got null here
see here
between some error is there at your 12th
number now you will go back and observe
what you have done so you will see that
you have written 11 12 times so this
kind of issues are also possible when
you are writing you need to First verify
if it is correct or not now you got all
the values
if any value you are missing at the
then
that will display null
yeah
Okay so
if anything other than this value that
you are coming then you can write simply
again if null on this
sorry
just to standardize the code
if null
of this entire statement and what you
want to tell not categorized
I'm writing it like this
and close it
sorry
like this
okay so this way also I can write it
click on apply
now again another use case uh we will
see so same thing and now you can try it
using if statement you can try to build
this using nestative and see if you are
able to you know achieve this again you
know it becomes interesting when you try
to write the same requirement in
multiple ways that is when we will be
able to understand the logical you know
part of it and we will be able to tune
ourselves so that we can write logic in
multiple ways so again the question for
you whoever is watching uh try to write
the same
thing in using IF
or I will say nested if
so that you understand better try it
that way if you are not getting let me
know I'll post it if you are getting it
do paste it in the comment section so
that you know it helps others who are
watching
now likewise you know one common
requirement we get in you know real time
is you know categorizing the values into
some different zones so assume here I've
got like 17 subcategory items now user
wanted to categorize into some groups
okay so one method is you know creating
a groups but I don't want to follow
group approach just because you know uh
you know
from learning perspective I want to use
some calculations so that I can derive
the value
so for that what I'm you writing is just
creating grouping uh sub category here
now here I want to group my
category into four parts or two to three
parts let us say as an example so again
many times I think I have asked in
interview but many failed to you know
write this
so today I'm trying to show you that we
are
categorizing subcategory
into
four parts
okay so how can we do now I'm writing
we can do it using multiple methods I am
using case statement because we are
learning about case so case of sub
category and I'm calling when it is uh
accessories so one important thing that
we need to understand about case
statement is whenever you are calling
this subcategory
Tableau will try to read all the values
that are there in that subcategory item
and it follows and it is case sensitive
okay
so I am just trying to pass whatever is
there in this when accessories then I
want to call it as like say group one
and when it is uh
appliances
so I'm just taking uh
alternate values here
at
then I'm calling it as group
to
when
binders
then it is group two
when it is uh
chairs
then I'm calling it as
group one so randomly I'm I'm passing it
okay so you can try any other method
that you feel again the purpose is to
show you how we can write okay and uh
when so anything okay so when it is one
more we need when it is a papers
and it is paper then it is
group
3
when it is uh
phone
then it is group three
any other than this
any other value that is coming apart
from this then it is group
4
me and it simply
okay now if I click on apply
and if I take it
onto column shelf see how my groups are
getting categorized so if I don't want
this I can simply take my remove this
and if I take subcategory into label
now it is clearly showing how many items
or how many subcategory items we have in
groups so if I aggregate this
see
so maybe account distinct we have
Group 1 has two subcategory items group
two has two group three has one and
group four has got
12 subcategory items this way also we
can
you know use this to create headers
so you know many times we create headers
on top of something so this kind of a
requirement also you can get it in real
time
okay now what I'm doing is uh just to
show you one more use case here let us
play with some a number here
and I'm trying to
okay so let us take sub category here
and I'm taking profit here
okay so we got profit okay now what I'm
doing is I'm taking
uh I'm writing a simple calculation here
and I'm trying to write
profit by
sales I am doing
okay so not profit so sum of
profit
by
Marcel
so some I'm just trying to divide it so
that I can see the value and I'm writing
here as uh
logical 4 here
and I'm just writing it or dragging it
here
I'll make it discrete
now it is giving us some value right
again the whole point here is just to
show you one use case here now instead
of this
I am trying to write 0 here
division by zero
so now we are getting null value
so now observe here even though our
calculation is valid here okay it is
giving us zero now I want to handle this
okay one of the very very important you
know requirement in real time how can we
handle this if
um
okay so I want to just uh
call it as uh
okay so replace some of
sales
with zero
let us see what is the okay so first let
us comment this
still we are getting replace is called
with the float do you mean string stick
oh
okay so see now we are getting string
error here so
if I try to convert it into string
okay
uh I'm trying to just hard code my sum
of sales to zero
directly zero so that I can
fix it
[Music]
okay so what I'm trying to do is I'm
simply trying to zero all my sum of
sales so see here how I'm doing if my
sum of sales
is not equal to 0 then I want to print
zero okay else also 0 and it okay I'm
just trying to test this
and you see I'm getting 0 here
now I'm just trying to divide this
with our sum of profit
divided by this
okay now
now what is happening see here
everything we are getting null now I
want to handle my denominator that when
we are getting this 0 or when our
denominator is basically zero I want to
display 0 that's it okay otherwise it
should display profit that is the
requirement here how can we do
is
uh
I'm taking this statement here
so whatever in the is there in the
denominator I'm taking that
is equal to
zero
then
print as zero okay
else
it would be sum of
profit farming
that's it
end it
okay so now something okay so we will
comment our these two parts and we'll
try to test it
yeah so now what will happen it will
test my denominator part so don't get
confused this is my denominator part if
this is somehow 0
then directly print the calculation as 0
because anything divided by 0 is
actually not calculatable so directly I
am creating that as 0 otherwise print my
sum of profit that is how I am trying to
do here click on apply now see here
we're doing it like this
okay so we can play it play with our
logics
like the way we want we just need to
understand how we can build that
so now if I try to write this here
and I'm just showing you another use
case here
so this is valid now if I click on this
I'm getting uh
null value now I want to handle this
whenever we are getting null I want to
replace it with 0. simply you can use
another function called as 0 or sorry ZN
function and you can simply cover this
up
now what will happen in all the cases
wherever you are getting null Tableau
will directly automatically replace it
with 0. click on apply
now you are getting zero one of the very
important function which is used to
standardize your code so
one example
if I take on this and if I try to
compute
quick table calculation like say percent
of total I am trying to do
now when I double click this it will
edit and if I copy
and paste it here
uh
see the code it is trying to do uh okay
now what I'm trying to do is uh
maybe I want to percentage difference I
am trying to do
yeah and control this thing
now see here the way calculation is
being generated by Tableau it is also
using ZN function to standardize the
code
see here apps function it is trying to
use now what it is doing
denominator cannot be
you know divided by negative number so
what it is doing it is using absolute
function to standardize the code
in denominator we don't need negative
value so that's why whatever is coming
in the denominator we are just removing
the negative number from that or
negative symbol from that and making it
absolute value
that way we can standardize our code so
this is one very good example for us to
understand that how Tableau will write
the code so the same format is extracted
from mu in real time as well
okay so that's it from what I said in
this video I hope this video has helped
you if it does don't forget to like
share and subscribe see you in the next
video have a good day and bye
浏览更多相关视频
If Else Statements in Python | Python for Beginners
Percabangan If dan Switch Case
How to Program A Calculator in C++! (Great C++ Microsoft Visual Studio Beginner Project)
C_63 Strings in C-part 2 | Read a String using scanf and gets function
Learn Python in Less than 10 Minutes for Beginners (Fast & Easy)
EVALUATING FUNCTIONS || GRADE 11 GENERAL MATHEMATICS Q1
5.0 / 5 (0 votes)