11 Replies Latest reply on Nov 19, 2018 2:45 PM by Joe Oppelt

    Receiving Null Values In Calculated Field That Are Not Null

    Tonia Spring

      I am using Tableau Desktop version 2018.2.  I cannot upload my packaged workbook because it contains HIPAA Info and it's data source is a MySQL Database.  I have a screen shot of the workbook and the calculated field.  I am looking to have the Sup determination Date from the Adult Assessment Tool (Assessment Section Question ID =4469) or from the Child Assessment Tool (Assessment Section Question ID =4473), unless the Assessment can be approved by the assessor and then I need the Assessor determination Date from the Adult Assessment Tool (Assessment Section Question ID =4467) or from the Child Assessment Tool (Assessment Section Question ID =4471).  In the screen shot below you can see each individual is returning the Sup Determination Date plus a Null value, Except ID Consumer # 132838.  That individual does not need Sup approval and should be returning the Assessor Determination Date.  Any assistance with this formula would be greatly appreciated.  Thank you.

       

        • 1. Re: Receiving Null Values In Calculated Field That Are Not Null
          Joe Oppelt

          First, if it turns out that you need to upload a workbook, you can anonymize your data (and chop out entire columns that aren't pertinent to the problem) as shown in the video linked here:

           

          Video demonstrates how to anonymize your workbook/data

           

          It doesn't matter what type of data source you have.  The steps end up with a little excel file that you swap into place for the sake of the sample workbook.

           

          ---

           

          Having said that, your calc is working at the row level.  I'm assuming you have multiple rows (perhaps differentiated by [Assessment Section question ID].

           

          You are setting a value if the ID is 4469 or 4473.  That's it.  Other rows are getting NULL.  It's not doing anything for 4467 or 4471.  Logic is only acting on 4469 and 4473 as it is set up here.

           

          What are you really looking to do here?

          • 2. Re: Receiving Null Values In Calculated Field That Are Not Null
            Tonia Spring

            I have added a workbook and the Excel doc.  There is only one individual populating to the report now.  Thank you for your time.

            • 3. Re: Receiving Null Values In Calculated Field That Are Not Null
              Joe Oppelt

              In the attached I have a sheet showing the question IDs. What do you want your calc to do with this data?

              • 4. Re: Receiving Null Values In Calculated Field That Are Not Null
                Tonia Spring

                I need the answers to Assessment Section Question ID# 4469 if it is an adult or Assessment Section Question ID# 4473 if it's a child and if the assessment did not need to be completed by a supervisor, I need the answers to Assessment Section Question ID# 4467 if it is an adult or Assessment Section Question ID# 4471 if it's a child.  This answer is a date of when the assessment determination was completed and called the Sup Determination Date.  The Sup Determination is the answers to Assessment Section Question ID# 4468 if it is an adult or Assessment Section Question ID# 4472 if it's a child and if the assessment did not need to be completed by a supervisor, I need the answers to Assessment Section Question ID# 4466 if it is an adult or Assessment Section Question ID# 4470 if it's a child.   This is an approval or denial of the assessment.  The problem I've been having is that I am receiving the date and a null value instead of just the date or approved or denied plus a null answer.  I do not need to count or sum up any data with this workbook.  Thank you for your help.

                • 5. Re: Receiving Null Values In Calculated Field That Are Not Null
                  Joe Oppelt

                  You have to help me out more.

                   

                  What determines if the Consumer is an adult or a child?  And let me know what fields control the other decision factors in your description.

                   

                  But one thing I think I understand -- for any given [ID Consumer], you want one individual value across all the rows under that Consumer for various returned values.  We certainly can do that.  I can show you one or two of them, and you'll be able to model the rest of them on the example I give.  I just need more info to do one for you.

                  • 6. Re: Receiving Null Values In Calculated Field That Are Not Null
                    Tonia Spring

                    Q1: The consumer's DOB determines if they are an adult or child.  The system users enter the information in will give them the appropriate assessment tool based on the consumer's age at the time of the assessment.

                     

                    Q2: The field assessment Id shows if it's an adult (4) or child (5) assessment tool.  Assessment Section Question Id is the field that determines which question is being answered from the assessment for the Sup Determination Date and Assessment Section Question Id (Assessment Options Answers) is the field that determines which question is being answered from the assessment for the Sup Determination.

                     

                    Q3: You are correct.

                     

                    Thank you for all your help.  I very much appreciate it.

                    • 7. Re: Receiving Null Values In Calculated Field That Are Not Null
                      Joe Oppelt

                      See attached.

                       

                      I'm doing FIXED LOD calcs.  (See [Tell me about 4669] and [Tell me about 4667].)

                       

                      These calcs go through all rows of each ID Consumer.  You can have all sorts of logic inside the parentheses.  For these two I check if the Question is the number I want, and if the criteria indicates I should collect the [Value].  Then, for ALL rows of the ID Consumer, this value gets set.

                       

                      And because it's a FIXED LOD, you can make it a dimension (as I did).  So you can do any sort of dimensional operations with it that you would do with DOB or Supervisor ID or any other dimension.

                      • 8. Re: Receiving Null Values In Calculated Field That Are Not Null
                        Tonia Spring

                        Is there a way to have just one of those?  If 4469 is blank then return 4467?  Thank you.

                        • 9. Re: Receiving Null Values In Calculated Field That Are Not Null
                          Joe Oppelt

                          In the case of your sample data, you have both, and you have [Value] on each of those rows. What would you want to do if you have them both -- and especially if they have different values?

                           

                          It seems to me that you will want to assess each separately.  Then you can have a final calc that can look at those two (or more) separate calcs and decide what to report in a single value.  Whether or not those individual calcs are on the sheet, they will have their values set so that a final calc can decide what you actually want to report on the sheet.

                           

                          But yes, you could embed all the individual chunks of logic into one giant calc if you wanted to.

                          • 10. Re: Receiving Null Values In Calculated Field That Are Not Null
                            Tonia Spring

                            I am receiving an error message when trying to add the below formula to the rows when the data source is the database and not the anonymize data from Excel.

                            { FIXED [ID Consumer] : MAX( if [Assessment Id] = 4 and [Assessment Section Question Id] = 4469

                                                         then [Value] END ) }

                             

                            I have copied the error message below.  Any suggestions?

                             

                             

                            [MySQL][ODBC 8.0(w) Driver][mysqld-5.6.10-log]Illegal mix of collations (utf8_bin,NONE) and (utf8_bin,NONE) for operation 'max('

                            SELECT (CASE WHEN ((`assessment_text_answers`.`assessment_section_question_id` = 4469) OR (`assessment_text_answers`.`assessment_section_question_id` = 4473)) THEN SUBSTRING(`assessment_text_answers`.`value`, 1, 1024) ELSE CAST(NULL AS CHAR(1)) END) AS `Assessor Determination Date (copy)`,

                              `t0`.`__measure__1` AS `Calculation_866661518484561933`,

                              `assessment_responses`.`id` AS `id (assessment_responses)`,

                              `consumers`.`id` AS `id`

                            FROM `consumers`

                              INNER JOIN `intake_events` ON (`consumers`.`id` = `intake_events`.`consumer_id`)

                              INNER JOIN `assessment_responses` ON (`consumers`.`id` = `assessment_responses`.`consumer_id`)

                              INNER JOIN `assessment_text_answers` ON (`assessment_responses`.`id` = `assessment_text_answers`.`assessment_response_id`)

                              INNER JOIN `county_view` ON (`consumers`.`county_id` = `county_view`.`id`)

                              INNER JOIN (

                              SELECT `consumers`.`id` AS `id`,

                                MAX((CASE WHEN ((`assessment_responses`.`assessment_id` = 4) AND (`assessment_text_answers`.`assessment_section_question_id` = 4469)) THEN SUBSTRING(`assessment_text_answers`.`value`, 1, 1024) ELSE CAST(NULL AS CHAR(1)) END)) AS `__measure__1`

                              FROM `consumers`

                                INNER JOIN `intake_events` ON (`consumers`.`id` = `intake_events`.`consumer_id`)

                                INNER JOIN `communication_notes` ON (`consumers`.`id` = `communication_notes`.`consumer_id`)

                                INNER JOIN `assessment_responses` ON (`consumers`.`id` = `assessment_responses`.`consumer_id`)

                                INNER JOIN `assessment_text_answers` ON (`assessment_responses`.`id` = `assessment_text_answers`.`assessment_response_id`)

                                INNER JOIN `assessment_option_answers` ON (`assessment_responses`.`id` = `assessment_option_answers`.`assessment_response_id`)

                                INNER JOIN `county_view` ON (`consumers`.`county_id` = `county_view`.`id`)

                              GROUP BY 1

                            ) `t0` ON (`consumers`.`id` = `t0`.`id`)

                            WHERE ((CONCAT(CONCAT(`consumers`.`last_name`, ', '), `consumers`.`first_name`) IN ('Allen, Angela', 'Alston, Julia', 'Benitez, Bless', 'Bush, Reva', 'Carper, Lisa', 'CHAMBERS, MARYJANE', 'Jones, Janice')) AND (`assessment_responses`.`assessment_id` IN (4, 5)) AND (`consumers`.`case_management_agency_id` = 4) AND (`assessment_responses`.`created_at` >= TIMESTAMP('2018-10-01 00:00:00')) AND (`assessment_responses`.`created_at` <= TIMESTAMP('2018-11-14 21:04:00')) AND (`intake_events`.`date` >= DATE('2018-08-01')) AND (`intake_events`.`date` <= DATE('2019-08-19')) AND (`assessment_responses`.`status` = 'Completed') AND (`consumers`.`status_id` IN (59, 61, 62)))

                            GROUP BY 1,

                              2,

                              3,

                              4

                            • 11. Re: Receiving Null Values In Calculated Field That Are Not Null
                              Joe Oppelt

                              I don't know what to do with that.

                               

                              By and large ODBC handles LODs.

                               

                              You might want to raise this to Support by filing a support ticket.

                              1 of 1 people found this helpful