Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
katchow002
New 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
Honored Contributor III

Re: How to do cross tables on load script?

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
New Contributor III

Re: How to do cross tables on load script?

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

sasiparupudi1
Honored Contributor III

Re: How to do cross tables on load script?

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
New Contributor III

Re: How to do cross tables on load script?

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
Honored Contributor III

Re: How to do cross tables on load script?

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

katchow002
New Contributor III

Re: How to do cross tables on load script?

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

katchow002
New Contributor III

Re: How to do cross tables on load script?

*for each cost item