topic Re: Count and Sum in Script execution in QlikView

Count and Sum in Script execution

markgraham123 — Wed, 22 Jul 2015 19:55:38 GMT

Hi,

Can someone help me in getting the count() function executed

Load

Place, Building, Location, Units, Bridge, Barcode, Units, Bay, Position, Level, Position&Level as PL, Bay&Location as BL

from table1;

I wanna get the Count(Barcode) by Place

Count(Barcode) by BL

Count(If(Units>0, Barcode) by Place

Count(If(Units>0, Barcode) by BL

Sum(Units) by Barcode;

Re: Count and Sum in Script execution

cspencer3 — Wed, 22 Jul 2015 20:27:02 GMT

Why does it need to be done in the load script and not in the App itself? Just to get a better understanding of what you would like to accomplish.

Re: Count and Sum in Script execution

markgraham123 — Wed, 22 Jul 2015 20:32:17 GMT

There millions of rows of data which is slowing down the app when its calcualted in front end.

Re: Count and Sum in Script execution

robert_mika — Wed, 22 Jul 2015 23:11:16 GMT

Could you post small sample of your data?

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 00:57:48 GMT

Hi,

Please find the attachment.

sunindia‌ Can u too have a look at this!

Re: Count and Sum in Script execution

robert_mika — Thu, 23 Jul 2015 01:04:42 GMT

I'm using PE.

Attached csv or xls

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 01:06:55 GMT

Hi,

PFA

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 01:08:18 GMT

Was trying this Sir.

Table1:

LOAD Warehouse,

LBCD,

Zone,

Units

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Table1)

Load Count(LBCD) as Total, Count(If(Units>0, LBCD)) as Filled Resident Table1 Group by Warehouse;

Left Join(Table1)

Load Sum(Units) as TOtalUnits Resident Table1 Group by LBCD;

Re: Count and Sum in Script execution

robert_mika — Thu, 23 Jul 2015 01:12:25 GMT

Really?

This is the same data set as per your first post....?

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 01:13:46 GMT

I created a sample data sir.

Re: Count and Sum in Script execution

settu_periasamy — Thu, 23 Jul 2015 02:05:01 GMT

Hi,

May be like this

Directory;  
Table1:  
LOAD Warehouse,   
     LBCD,   
     Zone,   
     Units  
FROM  
Book1.xlsx  
(ooxml, embedded labels, table is Sheet1);  
  
  
Left Join(Table1)  
Load Warehouse,LBCD,Count(LBCD) as Total,   
  Count(If(Units>0, LBCD)) as Filled,  
  Sum(Units) as Total_Units   
Resident Table1 Group by Warehouse,Zone,LBCD;

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 02:09:25 GMT

Hi,

Only if i load Warehouse, LBCD..while doing left join it works. Because i do not want to repeat the Warehouse in Table.

Is the below code correct???

Table1:

LOAD Warehouse,

LBCD,

Zone,

Units

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Table1)

LOAD Warehouse, Count(Distinct(LBCD)) as tt Resident Table1 Group By Warehouse;

Left Join (Table1)

LOAD LBCD, Sum(Units) as TOtalUnits Resident Table1 Group by LBCD;

Re: Count and Sum in Script execution

settu_periasamy — Thu, 23 Jul 2015 02:18:25 GMT

Yes, your code will work, Instead the below code will also give the same result.

Left Join(Table1)

LOAD Warehouse,

LBCD,

Count(Distinct(LBCD)) as tt,

Sum(Units) as TOtalUnits Resident Table1 Group By Warehouse,LBCD;

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 02:19:52 GMT

Thanq verymuch bro.

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 02:37:15 GMT

settu.periyasamy‌ sunindia‌

I was wondering

Why does it doesn't load when i use

Left Join

Load Sum(Units) Resident Table1 Groupy by LBCD;

Only works when i use,

Left Join

Load LBCD, Sum(Units) Resident Table1 Groupy by LBCD;

Any limelight pls...

Re: Count and Sum in Script execution

sunny_talwar — Thu, 23 Jul 2015 02:39:52 GMT

Because you are grouping by LBCD. When you use Group By certain field, it requires that field to be within the LOAD statement.

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 02:41:36 GMT

Thanq my friend...:)

Geek is back.

Re: Count and Sum in Script execution

settu_periasamy — Thu, 23 Jul 2015 02:43:47 GMT

Hi,

Why does it doesn't load when i use

Left Join

Load Sum(Units) Resident Table1 Groupy by LBCD;

When we are doing aggregation, we need to Load the Group by field also.

In this case, you didn't load the LBCD as field.

Re: Count and Sum in Script execution

markgraham123 — Thu, 23 Jul 2015 02:46:58 GMT

Thanq Bro.:)

Re: Count and Sum in Script execution

jagan — Thu, 23 Jul 2015 03:44:13 GMT

Hi,

Performing this type of calculations in front end is the better approach, also you are joining the values, sometimes there may be chances of data duplication.

So always do the Sum() and Count() in front end you will have lot of flexibility.

Regards,

Jagan.