Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
katchow002
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
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);

katchow002
Contributor III
Contributor III
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;

katchow002
Contributor III
Contributor III
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?

katchow002
Contributor III
Contributor III
Author

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

katchow002
Contributor III
Contributor III
Author

*for each cost item