topic Re: How to do cross tables on load script? in QlikView

How to do cross tables on load script?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to do cross tables on load script?

sasiparupudi1 — Mon, 16 Apr 2018 01:56:10 GMT

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

Re: How to do cross tables on load script?

Anonymous — Mon, 16 Apr 2018 02:04:46 GMT

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

Re: How to do cross tables on load script?

sasiparupudi1 — Mon, 16 Apr 2018 02:17:04 GMT

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;

Re: How to do cross tables on load script?

Anonymous — Mon, 16 Apr 2018 02:27:25 GMT

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.

Re: How to do cross tables on load script?

sasiparupudi1 — Mon, 16 Apr 2018 02:52:10 GMT

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

Re: How to do cross tables on load script?

Anonymous — Mon, 16 Apr 2018 02:55:26 GMT

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

Re: How to do cross tables on load script?

Anonymous — Mon, 16 Apr 2018 02:55:46 GMT

*for each cost item