Help on Cross Table

Anonymous — Mon, 23 Apr 2018 04:27:06 GMT

Hi everyone, I've been doing this task for a week but I still can't get the outcome. I am doing a QlikView which would show the Total Average Cost per Area.

Here is my load script:

T1:

LOAD [Account Code],

[Account Name],

[Site Code],

[Site Name],

[Site Year],

[Gross square feet (GSF)]

//[JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

//[Electrical - R&M],

//[Elevator - R&M],

//[Fire/life safety - R&M],

//[General building exterior - R&M],

//[General building interior - R&M],

//[HVAC - R&M],

//[Structural/roofing - R&M],

//[Plumbing - R&M],

//[Pest control within the building - R&M],

//[Miscellaneous / other - R&M]

FROM

(ooxml, embedded labels, table is Data);

Join

T2:

Load

[Site Name]&[Site Year] as key

Resident T1;

T3:

CrossTable(CostItem, Data, 1)

LOAD //[Account Code],

//[Account Name],

//[Site Code],

//[Site Name],

//[Site Year],

[Site Name]&[Site Year] as key,

//[Gross square feet (GSF)],

[JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

[Electrical - R&M],

[Elevator - R&M],

[Fire/life safety - R&M],

[General building exterior - R&M],

[General building interior - R&M],

[HVAC - R&M],

[Structural/roofing - R&M],

[Plumbing - R&M],

[Pest control within the building - R&M],

[Miscellaneous / other - R&M]

FROM

(ooxml, embedded labels, table is Data);

And my on my Expression Tab, script is:

Sum(Aggr(Avg(Data/[Gross square feet (GSF)]),[Account Name]))

I have to exclude zero and negative values upon load but when I do it with:

e.g.

Load [Gross square feet (GSF)],

If( [Gross square feet (GSF)]>0, [Gross square feet (GSF)])

Resident T1;

Load Data,

If(Data>0,Data)

Resident T3;

Nothing happens and when I run it takes a lot time for it to load.

Dimension is the Cost Item and Account Name? Actually it's just supposed to be CostItem but when I don't include Account Name as Dimension, the chart wouldn't display a grouped chart but instead it displays a stacked chart.

Where did I possibly go wrong? Please help and sorry in advance if I missed something so obvious as I am a beginner in QV.

Thanks in advance!

Re: Help on Cross Table

YoussefBelloum — Mon, 23 Apr 2018 08:16:51 GMT

Hi,

It is difficult to say what is wrong here without having seen the data structure and the expected output..

would you be able to share sample data and what you are expecting as an output ?

Re: Help on Cross Table

sunny_talwar — Mon, 23 Apr 2018 11:47:58 GMT

May be exclude nulls and 0s like this

T1:

LOAD [Account Code],

[Account Name],

[Site Code],

[Site Name],

[Site Year],

[Gross square feet (GSF)]

//[JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

//[Electrical - R&M],

//[Elevator - R&M],

//[Fire/life safety - R&M],

//[General building exterior - R&M],

//[General building interior - R&M],

//[HVAC - R&M],

//[Structural/roofing - R&M],

//[Plumbing - R&M],

//[Pest control within the building - R&M],

//[Miscellaneous / other - R&M]

FROM

(ooxml, embedded labels, table is Data)

Where [Gross square feet (GSF)] > 0 and Len(Trim([Gross square feet (GSF)])) > 0;

topic Re: Help on Cross Table in QlikView

Help on Cross Table

Re: Help on Cross Table

Re: Help on Cross Table