Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted
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);

Highlighted
Contributor III
Contributor III

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

Highlighted
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;

Highlighted
Contributor III
Contributor III

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.

Highlighted
Master III
Master III

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

*for each cost item