Mixing
IF part in tableau calculated field is not getting executed
I want to create a calculated field in tableau , where its value should be dependent on the value of the other field. Below is the calculation the calculated field [D]
IF [A]=NULL
THEN [B]
ELSE [C]
END
This calculated field always assigns the value in the else part ( [D] is populated with the value of [c]).
Note : [A],[B], and [C] are the existing fields in the tableau ( all are of string types)
tableau tableau-online tableau-public
asked Nov 19 '18 at 17:20
NewB
335
add a comment |
I want to create a calculated field in tableau , where its value should be dependent on the value of the other field. Below is the calculation the calculated field [D]
IF [A]=NULL
THEN [B]
ELSE [C]
END
This calculated field always assigns the value in the else part ( [D] is populated with the value of [c]).
Note : [A],[B], and [C] are the existing fields in the tableau ( all are of string types)
tableau tableau-online tableau-public
asked Nov 19 '18 at 17:20
NewB
335
add a comment |
I want to create a calculated field in tableau , where its value should be dependent on the value of the other field. Below is the calculation the calculated field [D]
IF [A]=NULL
THEN [B]
ELSE [C]
END
This calculated field always assigns the value in the else part ( [D] is populated with the value of [c]).
Note : [A],[B], and [C] are the existing fields in the tableau ( all are of string types)
tableau tableau-online tableau-public
asked Nov 19 '18 at 17:20
NewB
335
I want to create a calculated field in tableau , where its value should be dependent on the value of the other field. Below is the calculation the calculated field [D]
IF [A]=NULL
THEN [B]
ELSE [C]
END
This calculated field always assigns the value in the else part ( [D] is populated with the value of [c]).
Note : [A],[B], and [C] are the existing fields in the tableau ( all are of string types)
tableau tableau-online tableau-public
tableau tableau-online tableau-public
asked Nov 19 '18 at 17:20
NewB
335
asked Nov 19 '18 at 17:20
NewB
335
asked Nov 19 '18 at 17:20
NewB
335
asked Nov 19 '18 at 17:20
NewB
335
asked Nov 19 '18 at 17:20
NewB
335
335
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
As others have pointed out, you can use ISNULL() to test whether a field has a null value. Testing for the presence or absence of a value using ISNULL() is different than testing a value with equality
This is not exactly the case you asked about, but sometimes, you may have a default value for a field that you always want to use in cases when there is no value specified, in that case, the IFNULL() function is useful. It returns the value of its first argument if there is a non-null value, otherwise it returns the value of the second argument.
Let's say you have a field called Approver, and if no Approver is specified in the data, then the default value is "Fred". A reliable approach is to first:
- Rename the Approver field in Tableau to, say, Approver-Original
- Define a new Approver field as
ifnull(Approver-Original, "Fred")
- Hide the field Approver-Original
Then you can safely use Approver anywhere you wish knowing that it always has a value, and the information about applying defaults is in one place.
There is also a function called ZN() for numeric field that returns zero if the argument is null. Useful in those cases where zero is the correct default.
All this makes sense in cases, where it makes sense to have a default value to replace null.
There are other cases when you really want to leave a value null to represent the absence of data, say a field called Spouse that will be null for single people. Assigning a default spouse in that case would be wrong, and possibly not appreciated. ISNULL() is useful in that case.
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
add a comment |
Try this:
IF ISNULL([A])
THEN [B]
ELSE [C]
END
answered Nov 19 '18 at 17:31
vizyourdata
57411031
add a comment |
A Null value in a field means there is nothing in the field and it is not storing a value NULL.
If you really want to compare NULL in a field then use as IF ISNULL([A]). With file based data sources such as Excel or CSV, you can also check for an empty string with IF [A]='' — since those data sources don’t distinguish between nulls and empty strings.
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:00
Siva
7,61282847
Testing for equality with an empty string is not a valid test for a null value.
– Alex Blakemore
Nov 19 '18 at 18:03
@AlexBlakemore It is a raw way of comparing a NULL value though you have a functionISNULLfor the same and it works
– Siva
Nov 19 '18 at 18:04
I'm not sure I understand your comment. Are you saying that if you have a character field called Name in a database such as SQL Server, and there is a null value a row in the Name field, that "Name=''" will return true in that case? Or are you only thinking of file sources like Excel?
– Alex Blakemore
Nov 19 '18 at 18:26
@AlexBlakemore yes you are correct, It will return true case
– Siva
Nov 20 '18 at 9:07
ok, I believe that is an artifact of using a file based data source such as Excel or csv, and that data sources built on true databases treat null and empty string differently (i.e. the test will return false with a database data source). I’ll edit the answer to clarify that and remove my downvote
– Alex Blakemore
Nov 21 '18 at 0:11
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379712%2fif-part-in-tableau-calculated-field-is-not-getting-executed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
As others have pointed out, you can use ISNULL() to test whether a field has a null value. Testing for the presence or absence of a value using ISNULL() is different than testing a value with equality
This is not exactly the case you asked about, but sometimes, you may have a default value for a field that you always want to use in cases when there is no value specified, in that case, the IFNULL() function is useful. It returns the value of its first argument if there is a non-null value, otherwise it returns the value of the second argument.
Let's say you have a field called Approver, and if no Approver is specified in the data, then the default value is "Fred". A reliable approach is to first:
- Rename the Approver field in Tableau to, say, Approver-Original
- Define a new Approver field as
ifnull(Approver-Original, "Fred")
- Hide the field Approver-Original
Then you can safely use Approver anywhere you wish knowing that it always has a value, and the information about applying defaults is in one place.
There is also a function called ZN() for numeric field that returns zero if the argument is null. Useful in those cases where zero is the correct default.
All this makes sense in cases, where it makes sense to have a default value to replace null.
There are other cases when you really want to leave a value null to represent the absence of data, say a field called Spouse that will be null for single people. Assigning a default spouse in that case would be wrong, and possibly not appreciated. ISNULL() is useful in that case.
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
add a comment |
As others have pointed out, you can use ISNULL() to test whether a field has a null value. Testing for the presence or absence of a value using ISNULL() is different than testing a value with equality
This is not exactly the case you asked about, but sometimes, you may have a default value for a field that you always want to use in cases when there is no value specified, in that case, the IFNULL() function is useful. It returns the value of its first argument if there is a non-null value, otherwise it returns the value of the second argument.
Let's say you have a field called Approver, and if no Approver is specified in the data, then the default value is "Fred". A reliable approach is to first:
- Rename the Approver field in Tableau to, say, Approver-Original
- Define a new Approver field as
ifnull(Approver-Original, "Fred")
- Hide the field Approver-Original
Then you can safely use Approver anywhere you wish knowing that it always has a value, and the information about applying defaults is in one place.
There is also a function called ZN() for numeric field that returns zero if the argument is null. Useful in those cases where zero is the correct default.
All this makes sense in cases, where it makes sense to have a default value to replace null.
There are other cases when you really want to leave a value null to represent the absence of data, say a field called Spouse that will be null for single people. Assigning a default spouse in that case would be wrong, and possibly not appreciated. ISNULL() is useful in that case.
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
add a comment |
As others have pointed out, you can use ISNULL() to test whether a field has a null value. Testing for the presence or absence of a value using ISNULL() is different than testing a value with equality
This is not exactly the case you asked about, but sometimes, you may have a default value for a field that you always want to use in cases when there is no value specified, in that case, the IFNULL() function is useful. It returns the value of its first argument if there is a non-null value, otherwise it returns the value of the second argument.
Let's say you have a field called Approver, and if no Approver is specified in the data, then the default value is "Fred". A reliable approach is to first:
- Rename the Approver field in Tableau to, say, Approver-Original
- Define a new Approver field as
ifnull(Approver-Original, "Fred")
- Hide the field Approver-Original
Then you can safely use Approver anywhere you wish knowing that it always has a value, and the information about applying defaults is in one place.
There is also a function called ZN() for numeric field that returns zero if the argument is null. Useful in those cases where zero is the correct default.
All this makes sense in cases, where it makes sense to have a default value to replace null.
There are other cases when you really want to leave a value null to represent the absence of data, say a field called Spouse that will be null for single people. Assigning a default spouse in that case would be wrong, and possibly not appreciated. ISNULL() is useful in that case.
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
As others have pointed out, you can use ISNULL() to test whether a field has a null value. Testing for the presence or absence of a value using ISNULL() is different than testing a value with equality
This is not exactly the case you asked about, but sometimes, you may have a default value for a field that you always want to use in cases when there is no value specified, in that case, the IFNULL() function is useful. It returns the value of its first argument if there is a non-null value, otherwise it returns the value of the second argument.
Let's say you have a field called Approver, and if no Approver is specified in the data, then the default value is "Fred". A reliable approach is to first:
- Rename the Approver field in Tableau to, say, Approver-Original
- Define a new Approver field as
ifnull(Approver-Original, "Fred")
- Hide the field Approver-Original
Then you can safely use Approver anywhere you wish knowing that it always has a value, and the information about applying defaults is in one place.
There is also a function called ZN() for numeric field that returns zero if the argument is null. Useful in those cases where zero is the correct default.
All this makes sense in cases, where it makes sense to have a default value to replace null.
There are other cases when you really want to leave a value null to represent the absence of data, say a field called Spouse that will be null for single people. Assigning a default spouse in that case would be wrong, and possibly not appreciated. ISNULL() is useful in that case.
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:31
Alex Blakemore
8,20921840
8,20921840
add a comment |
add a comment |
Try this:
IF ISNULL([A])
THEN [B]
ELSE [C]
END
answered Nov 19 '18 at 17:31
vizyourdata
57411031
add a comment |
Try this:
IF ISNULL([A])
THEN [B]
ELSE [C]
END
answered Nov 19 '18 at 17:31
vizyourdata
57411031
add a comment |
Try this:
IF ISNULL([A])
THEN [B]
ELSE [C]
END
answered Nov 19 '18 at 17:31
vizyourdata
57411031
Try this:
IF ISNULL([A])
THEN [B]
ELSE [C]
END
answered Nov 19 '18 at 17:31
vizyourdata
57411031
answered Nov 19 '18 at 17:31
vizyourdata
57411031
answered Nov 19 '18 at 17:31
vizyourdata
57411031
answered Nov 19 '18 at 17:31
vizyourdata
57411031
57411031
add a comment |
add a comment |
A Null value in a field means there is nothing in the field and it is not storing a value NULL.
If you really want to compare NULL in a field then use as IF ISNULL([A]). With file based data sources such as Excel or CSV, you can also check for an empty string with IF [A]='' — since those data sources don’t distinguish between nulls and empty strings.
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:00
Siva
7,61282847
Testing for equality with an empty string is not a valid test for a null value.
– Alex Blakemore
Nov 19 '18 at 18:03
@AlexBlakemore It is a raw way of comparing a NULL value though you have a functionISNULLfor the same and it works
– Siva
Nov 19 '18 at 18:04
I'm not sure I understand your comment. Are you saying that if you have a character field called Name in a database such as SQL Server, and there is a null value a row in the Name field, that "Name=''" will return true in that case? Or are you only thinking of file sources like Excel?
– Alex Blakemore
Nov 19 '18 at 18:26
@AlexBlakemore yes you are correct, It will return true case
– Siva
Nov 20 '18 at 9:07
ok, I believe that is an artifact of using a file based data source such as Excel or csv, and that data sources built on true databases treat null and empty string differently (i.e. the test will return false with a database data source). I’ll edit the answer to clarify that and remove my downvote
– Alex Blakemore
Nov 21 '18 at 0:11
add a comment |
A Null value in a field means there is nothing in the field and it is not storing a value NULL.
If you really want to compare NULL in a field then use as IF ISNULL([A]). With file based data sources such as Excel or CSV, you can also check for an empty string with IF [A]='' — since those data sources don’t distinguish between nulls and empty strings.
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:00
Siva
7,61282847
Testing for equality with an empty string is not a valid test for a null value.
– Alex Blakemore
Nov 19 '18 at 18:03
@AlexBlakemore It is a raw way of comparing a NULL value though you have a functionISNULLfor the same and it works
– Siva
Nov 19 '18 at 18:04
I'm not sure I understand your comment. Are you saying that if you have a character field called Name in a database such as SQL Server, and there is a null value a row in the Name field, that "Name=''" will return true in that case? Or are you only thinking of file sources like Excel?
– Alex Blakemore
Nov 19 '18 at 18:26
@AlexBlakemore yes you are correct, It will return true case
– Siva
Nov 20 '18 at 9:07
ok, I believe that is an artifact of using a file based data source such as Excel or csv, and that data sources built on true databases treat null and empty string differently (i.e. the test will return false with a database data source). I’ll edit the answer to clarify that and remove my downvote
– Alex Blakemore
Nov 21 '18 at 0:11
add a comment |
A Null value in a field means there is nothing in the field and it is not storing a value NULL.
If you really want to compare NULL in a field then use as IF ISNULL([A]). With file based data sources such as Excel or CSV, you can also check for an empty string with IF [A]='' — since those data sources don’t distinguish between nulls and empty strings.
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:00
Siva
7,61282847
A Null value in a field means there is nothing in the field and it is not storing a value NULL.
If you really want to compare NULL in a field then use as IF ISNULL([A]). With file based data sources such as Excel or CSV, you can also check for an empty string with IF [A]='' — since those data sources don’t distinguish between nulls and empty strings.
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
answered Nov 19 '18 at 18:00
Siva
7,61282847
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
edited Nov 21 '18 at 0:18
Alex Blakemore
8,20921840
8,20921840
answered Nov 19 '18 at 18:00
Siva
7,61282847
answered Nov 19 '18 at 18:00
Siva
7,61282847
answered Nov 19 '18 at 18:00
Siva
7,61282847
7,61282847
Testing for equality with an empty string is not a valid test for a null value.
– Alex Blakemore
Nov 19 '18 at 18:03
@AlexBlakemore It is a raw way of comparing a NULL value though you have a functionISNULLfor the same and it works
– Siva
Nov 19 '18 at 18:04
I'm not sure I understand your comment. Are you saying that if you have a character field called Name in a database such as SQL Server, and there is a null value a row in the Name field, that "Name=''" will return true in that case? Or are you only thinking of file sources like Excel?
– Alex Blakemore
Nov 19 '18 at 18:26
@AlexBlakemore yes you are correct, It will return true case
– Siva
Nov 20 '18 at 9:07
ok, I believe that is an artifact of using a file based data source such as Excel or csv, and that data sources built on true databases treat null and empty string differently (i.e. the test will return false with a database data source). I’ll edit the answer to clarify that and remove my downvote
– Alex Blakemore
Nov 21 '18 at 0:11
add a comment |
Testing for equality with an empty string is not a valid test for a null value.
– Alex Blakemore
Nov 19 '18 at 18:03
@AlexBlakemore It is a raw way of comparing a NULL value though you have a functionISNULLfor the same and it works
– Siva
Nov 19 '18 at 18:04
I'm not sure I understand your comment. Are you saying that if you have a character field called Name in a database such as SQL Server, and there is a null value a row in the Name field, that "Name=''" will return true in that case? Or are you only thinking of file sources like Excel?
– Alex Blakemore
Nov 19 '18 at 18:26
@AlexBlakemore yes you are correct, It will return true case
– Siva
Nov 20 '18 at 9:07
ok, I believe that is an artifact of using a file based data source such as Excel or csv, and that data sources built on true databases treat null and empty string differently (i.e. the test will return false with a database data source). I’ll edit the answer to clarify that and remove my downvote
– Alex Blakemore
Nov 21 '18 at 0:11
Testing for equality with an empty string is not a valid test for a null value.
– Alex Blakemore
Nov 19 '18 at 18:03
Testing for equality with an empty string is not a valid test for a null value.
– Alex Blakemore
Nov 19 '18 at 18:03
@AlexBlakemore It is a raw way of comparing a NULL value though you have a function
ISNULL for the same and it works– Siva
Nov 19 '18 at 18:04
@AlexBlakemore It is a raw way of comparing a NULL value though you have a function
ISNULL for the same and it works– Siva
Nov 19 '18 at 18:04
I'm not sure I understand your comment. Are you saying that if you have a character field called Name in a database such as SQL Server, and there is a null value a row in the Name field, that "Name=''" will return true in that case? Or are you only thinking of file sources like Excel?
– Alex Blakemore
Nov 19 '18 at 18:26
I'm not sure I understand your comment. Are you saying that if you have a character field called Name in a database such as SQL Server, and there is a null value a row in the Name field, that "Name=''" will return true in that case? Or are you only thinking of file sources like Excel?
– Alex Blakemore
Nov 19 '18 at 18:26
@AlexBlakemore yes you are correct, It will return true case
– Siva
Nov 20 '18 at 9:07
@AlexBlakemore yes you are correct, It will return true case
– Siva
Nov 20 '18 at 9:07
ok, I believe that is an artifact of using a file based data source such as Excel or csv, and that data sources built on true databases treat null and empty string differently (i.e. the test will return false with a database data source). I’ll edit the answer to clarify that and remove my downvote
– Alex Blakemore
Nov 21 '18 at 0:11
ok, I believe that is an artifact of using a file based data source such as Excel or csv, and that data sources built on true databases treat null and empty string differently (i.e. the test will return false with a database data source). I’ll edit the answer to clarify that and remove my downvote
– Alex Blakemore
Nov 21 '18 at 0:11
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379712%2fif-part-in-tableau-calculated-field-is-not-getting-executed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
