6 Replies Latest reply: Mar 28, 2016 1:37 PM by Linsey Kempton RSS

    Drill Down with multiple fields

    Linsey Kempton

      I want to create a drill down on my bar chart. The first layer will be the date which is no problem as the raw data is already grouped by date. I have to identify the specific project so my expression reads:

          

       

      =If(Project='ProjectXYZ',Date)

       

      The next layer I want to drill down to will be by resource. The problem is, the data is coming through with each resource showing as their own field.

      The script is showing:

       

      LOAD Date,

                Resource 1,

                Resource 2,

                Total,

                Needed,

                'ProjectXYZ' as Project

       

       

      Resource 1 and Resource 2 have the hours they are allocated to that specific project for that specific week. I want to take both of these fields and group them into a 'Resource' field. That way my second level on the drill down will be this field - So when I click on a specific date, it breaks that date out by resource.

        • Re: Drill Down with multiple fields
          eric Johnson

          It sounds like the issue is that you need one resource dimension for the bar chart but have the data in two fields.

           

          Could you load the table twice and concatenate the resource 1 and 2 fields into one field?

           

          Something like...

           

          DataTable:

          LOAD Date,

                    Resource 1 as Resource,

                    R1 as [Resource Type]

                    Total,

                    Needed,

                    'ProjectXYZ' as Project;

           

          Res2Table:

          CONCATENATE(DataTable)

          LOAD Date,

                    Resource 2 as Resource,

                    R2 as [Resource Type]

                    Total,

                    Needed,

                    'ProjectXYZ' as Project;

           

          Then your dimension is resource type and expression involves Resource variable for hours.

          • Re: Drill Down with multiple fields
            Linsey Kempton

            Thanks Eric,

            I will try this,

            I wanted to correct my original script I load. I will need to use the Resource 1 and Resource 2 fields for other dashboard elements. Will you suggestion eliminate that possibility?

              • Re: Drill Down with multiple fields
                eric Johnson

                Not at all, just add those back into the loads. I am not sure if you need to add the rvariables into the concatenated load or not, so assuming you do, I just added them in as '0' records as not to duplicate the sums of resource 1 or resource 2. You'll need to check how that is handling Total and Needed in your actual script as well.

                 

                DataTable:

                LOAD Date,

                          Resource 1,

                          Resource 2,

                          Resource 1 as Resource,

                          R1 as [Resource Type],

                          Total,

                          Needed,

                          'ProjectXYZ' as Project;

                 

                Res2Table:

                CONCATENATE(DataTable)

                LOAD Date,

                          '0' as Resource 1,

                          '0' as Resource 2,

                          Resource 2 as Resource,

                          R2 as [Resource Type],

                          Total,

                          Needed,

                          'ProjectXYZ' as Project;

              • Re: Drill Down with multiple fields
                Linsey Kempton

                Also,

                I am having issues loading the script. The semicolon after the first table load after Project; is causing the  FROM to show up with a correction mark under the entire path.

                  • Re: Drill Down with multiple fields
                    Bill Markham

                    Did you sort the missing comma's ?

                     

                    DataTable:

                    LOAD Date,

                              Resource 1 as Resource,

                              R1 as [Resource Type] ,

                              Total,

                              Needed,

                              'ProjectXYZ' as Project

                    From Your.qvd

                    ;

                     

                    Res2Table:

                    CONCATENATE(DataTable)

                    LOAD Date,

                              Resource 2 as Resource,

                              R2 as [Resource Type] ,

                              Total,

                              Needed,

                              'ProjectXYZ' as Project

                    From Your.qvd

                    ;

                     

                    I am guessing re your actual load script.  Maybe you could share it ?

                  • Re: Drill Down with multiple fields
                    Linsey Kempton

                    Thanks! So when I run the following Script:

                     

                    DateTable:

                    LOAD Date,

                         Cobran,

                         Osman,

                         Cobran as Resource,

                         R1 as [Resource Type],

                         Total,

                         Needed,

                         'Cherry Wood' as Project;

                        

                    Res2Table:

                    LOAD Date,

                      '0' as Cobran,

                      '0' as Osman,

                      Osman as Resource,

                      R2 as [Resource Type],

                      Total,

                      Needed,

                      'Cherry Wood' as Project;    

                        

                    FROM

                    [C:\Users\lkempton\Desktop\Qlikview\Project Runway\Data.xlsx]

                    (ooxml, embedded labels, table is [Cherry Wood Support]);

                     

                    I receive the message 'Unknown Statement From:

                    [C:\Users\lkempton\Desktop\Qlikview\Project Runway\Data.xlsx]

                    (ooxml, embedded labels, table is [Cherry Wood Support]);

                     

                    The Excel Doc (Data.xlsx) is set up with the following columns on the tab 'Cherry Wood Support'

                    Date

                    Cobran

                    Osman

                    Total

                    Needed