topic How to remove Totals and other unwanted rows using Script? in QlikView

How to remove Totals and other unwanted rows using Script?

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

pradosh_thakur‌ its.anandrjs‌

Hi,

I have the attached file/table to load.

Have to calculate model wise Sum(Cost)/Sum(Qty) as Exp.

Also need to ensure that:

1. The totals are removed (highlighted in Red & Blue)

2. If a model is repeated, then the average of repeated rows has to be considered:

e.g in case of FTP-005 the Exp. would be 18333.34 i.e (100000/15) + (60000/2)) / 2

Looking forward to your help,

Nick

Re: How to remove Totals and other unwanted rows using Script?

its_anandrjs — Sun, 08 Oct 2017 10:52:20 GMT

Load your table this way to not consider the rows where you have Total on that row and make clean on the load script and then do calculation for Exp that you have and take to table itself.

Data:

LOAD RowNo() as RowID,

FieldA,

[Field Center],

Model,

Quantity,

Cost

FROM

[Value by Sheet Aug17 - CSD.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where right([Field Center],5) <> 'Total' and Right(FieldA,5) <> 'Total';

Left Join

LOAD

Model,RowID,

Sum(Cost)/Sum(Quantity) as Exp

Resident Data Group By Model,RowID;

Re: How to remove Totals and other unwanted rows using Script?

nickjose7 — Sun, 08 Oct 2017 12:17:32 GMT

Hi Anand,

If a model is repeated, then the average of repeated rows has to be considered:

e.g in case of FTP-005 the Exp. would be 18333.34 i.e (100000/15) + (60000/2)) / 2

Re: How to remove Totals and other unwanted rows using Script?

pradosh_thakur — Tue, 10 Oct 2017 13:10:28 GMT

Hi nick

Please find the attached. Named after you.

regards

Pradosh