Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Drill Down with multiple fields

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.

6 Replies
erjohnso
Contributor

Re: Drill Down with multiple fields

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.

Not applicable

Re: Drill Down with multiple fields

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?

Not applicable

Re: Drill Down with multiple fields

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

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 ?

erjohnso
Contributor

Re: Drill Down with multiple fields

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;

Not applicable

Re: Drill Down with multiple fields

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

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

I receive the message 'Unknown Statement From:

(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

Community Browser