Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to do cross tables on load script?

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.

7 Replies
sasiparupudi1
Master III
Master III

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
Not applicable
Author

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

sasiparupudi1
Master III
Master III

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
Not applicable
Author

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:

IMG_16042018_093907_0.png

Thank you once again.

sasiparupudi1
Master III
Master III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

*for each cost item