How to do cross tables on load script?

[Anonymous]

Hi guys,

How do I create a cross table where the cost data should be shown in one column across a number of cost items, while excluding zero and negative values upon loading?

Here's the excel file with the instructions in it.

Thanks in advance for your help.

[sasiparupudi1]

May be like this?

CrossTable(Data,Value,5)

LOAD [Account Code],

     [Account Name],

     [Site Code],

     [Site Name],

     [Site Year],

     [Gross square feet (GSF)],

     [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

[ Data Training.xlsx]

(ooxml, embedded labels, table is Data);

[Anonymous]

Thanks Sir, but how do I exclude negative and zero values on the load script?

[sasiparupudi1]

Try a resident load

T1:

CrossTable(Data,Value,5)

LOAD [Account Code],

     [Account Name],

     [Site Code],

     [Site Name],

     [Site Year],

     [Gross square feet (GSF)],

     [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)

;

Final:

NoConcatenate Load

*

Resident T1

WHERE

Len(Trim(Value))=0 Or Value>0;

Drop Table T1;

[Anonymous]

Hi again Sir, I run it without errors but I can't see the results since I haven't made the expression for loading the stacked bar chart for the Cost/GSF. May you please help me on this as well. I am a newbie in QV and is still navigating my way through it.

This should be the resulting QV:

Thank you once again.

[sasiparupudi1]

What is not working? can you please share your app? what is Cost per GSF?

[Anonymous]

Here sir, please see attached excel. The instruction is to get the Cost/GSF for each item.

[Anonymous]

*for each cost item