topic Re: Drill Down with multiple fields in QlikView

Drill Down with multiple fields

Tue, 22 Mar 2016 20:30:21 GMT

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

erjohnso — Tue, 22 Mar 2016 20:45:41 GMT

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

Thu, 24 Mar 2016 14:46:31 GMT

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

Thu, 24 Mar 2016 19:34:56 GMT

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

Anonymous — Thu, 24 Mar 2016 19:58:16 GMT

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

erjohnso — Mon, 28 Mar 2016 16:54:16 GMT

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

Mon, 28 Mar 2016 17:37:46 GMT

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