9 Replies Latest reply on Aug 14, 2018 5:53 AM by nicolas sinquin

    Trouble with count distinct and external kml file

    nicolas sinquin

      Hye,

       

      I'm quite new on Tableau, and i'm in trouble.

       

      I'v got an Oracle table with Id_Fam, Year, Id_Loc

      On other with Id_Loc, District_Name

      In an other connexion, i've a kml file with name as attribute witch is the district name.

       

      I do my map, and have distinct id_fam by district.

      When i put annee as filter, i've the message "Impossible de fusionner la source de données secondaire, car un ou plusieurs champs utilisent une agrégation non prise en charge"

      (cannot blend the secondary data source because one or more fields use an unsupported aggregation)

       

      If i select without distinct, it's ok (but not what I want.

       

      I put a twb file.

       

      Thanks for your help

       

      Ce message a été modifié par : nicolas sinquin

        • 1. Re: Trouble with count distinct and external kml file
          Joe Oppelt

          COUNTD requires Tableau to look at the data on a row-by-row basis, but a blend can only see an aggregate.

           

          I'll take a look at your workbook and see if I can recommend something.

          • 2. Re: Trouble with count distinct and external kml file
            Joe Oppelt

            See attached.


            I made a FIXED LOD in the secondary source that I could change into a dimension.  Now the evaluation happens before the blend occurs, and you have access to that value.  I think this is getting you what you need.

             

            Note that when you initially drag this onto your Sheet 9, it will go on as a DISCRETE pill (blue pill) so when you use it on the colors shelf you get a whole bunch of different colors rather than a spectrum of blue shades.  But I edited the pill to change it from discrete to continuous, and that gives the dark-to-light shading.

            • 3. Re: Trouble with count distinct and external kml file
              nicolas sinquin

              Hye Joe,

              thanks for your help.

              I think i understand what you did.

               

              It's nearly what i'm looking for. I tried, but I also want the number into tooltip.

               

              I also have checked the result, and it doesnt seems to be correct. For exemple, for year 2014 and district 'ZONE NATURELLE' in my base i found 22 distinct ID_FAM, on the map it's 40.

              • 4. Re: Trouble with count distinct and external kml file
                Joe Oppelt

                I just created Calc 1 at the level of [Quarter].  If you want individual year values, just add that to the LOD.

                 

                In the attached I modified Calc1 to compute at the level of quarter and year.  I made Sheet 2 to display the values.

                 

                Then I tried to use it on Sheet 9.  Well, that compartmentalizes the map at both [Name] and Year.  And if I did SUM(Calculation1]) I got the same error as you were getting before.

                 

                So I made Calculation2.  This is another LOD to sum up the years for each [Quarter].

                 

                Now I can use that on Sheet9.


                The point is to do row-level work in the secondary source, so that when the blend pulls data it doesn't have to do the row-level work.


                Also, in the attached I have added both Calc1 and Calc2 to the tooltip in Sheet 9.  Notice that Calc1 gives you an asterisk if there are multiple values returned from the secondary source.  Change the year filter to grab just 2014 and you'll get 22 in ZONE NATURELLE for calc1, for example.  Keep in mind that no matter what years you select here, calc2 will always be 205 for Zone Naturelle.  That's because Tableau is doing the work in all the data of the secondary source before pulling anything into the sheet's underlying table.

                 

                Also, I'm not sure why the earlier version of this was getting 40 for Zone Naturelle.  It should be 205, as shown on Sheet 2.  The calcs are doing the right thing now.

                 

                And one final thing.  If you want the map to be filtered by year from the secondary source, we'll have to do something different than just putting the secondary field on the filter shelf.  The LOD isn't governed by that filter.  (That's how FIXED LODs work.)  Perhaps we can do a Start-year and an end-year parameter.  You'll be able to control the fixed LODs that way.

                • 5. Re: Trouble with count distinct and external kml file
                  nicolas sinquin

                  Hye Joe,

                   

                  yes, thanks, it seems to be right !

                   

                  As all of my maps will turn around the district, i'll do the same for each variable ?

                   

                  First, i'll try to understand what you did.

                  • 6. Re: Trouble with count distinct and external kml file
                    Joe Oppelt

                    Yup.  You'll have to do it for each variable -- for those that you want COUNTD.  If you just want COUNT, this won't be necessary.  Ditto SUM and other aggregates.

                    • 7. Re: Trouble with count distinct and external kml file
                      nicolas sinquin

                      Ok  thanks.

                       

                      Last question, I tried to have nb_enf as filter for exemple, but nothing happens when i select nb_enf (what i'm looking for, it's the number of family by district with 1,2,or 3 child for exemple).

                      Have I to put also this variable into the LOD ? (i tried, but unsuccesfully ....)

                      • 8. Re: Trouble with count distinct and external kml file
                        Joe Oppelt

                        In Tableau's order of operation, filters are evaluated AFTER a FIXED LOD is evaluated.  On Sheet 2 I have added [Nb Enf] to the filters, and selected for 1,2,3.  You can see that no matter what you do with that filter, the values on the sheet stay the same because they are computed by FIXED LODs.

                         

                        Go to Sheet 2(2).  Tableau has given us a way to force the filter to be evaluated BEFORE the FIXED LODs.  By right clicking the filter and adding it to CONTEXT, the LOD gets evaluated AFTER the filter.  Pretty nice.  So you can have some filters in context, and some out of context, and control where those filters get evaluated in relation to FIXED LODs.

                         

                        But that's not  the end of it.  On your sheet 9, you are using those LODs from the secondary source.  We're not going to have values 1-through-13 there for SUM([Nb Enf]).  (Put it on the sheet and see that you're getting the sum across each quartier, and therefore you get giant values for the field.)

                         

                        You're going to have to make that evaluation happen in the FIXED LOD as you surmised.

                         

                        On Sheet 2(3) I simulated what the NB ENF filter was doing on 2(2).  I made a FIXED calc to capture the sum of [Nb Enf] for each row by evaluating the sum at the level of all the dimensions in BI_FAMILLE_H_2 (which is where [Nb Enf] is coming from.)  See [Nb Enf to use].  Next look at [Calculation1 (copy)].  In there I do the COUNTD only if the value to use is 1, 2 or 3.  You can see that no matter what you do with the filters, you get the same 209 value in the first slot that you do on Sheet 2(2) that was using the in-context filter.

                         

                        Yes, this is a bit complicated.  The need to use sums and countD values from the secondary source forces us to create all these FIXED LODs so that the value can be used at the dimension level in the blend.

                        1 of 1 people found this helpful
                        • 9. Re: Trouble with count distinct and external kml file
                          nicolas sinquin

                          Thank you again.

                          I think i start understanding how it works.