Count and Sum in Script execution

[markgraham123]

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;

[settu_periasamy]

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; 

[markgraham123]

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;

[settu_periasamy · Accepted solution]

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;

[markgraham123]

Thanq verymuch bro.

[markgraham123]

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...

[sunny_talwar]

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

[markgraham123]

Thanq my friend...:)

Geek is back.

[settu_periasamy]

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.

[markgraham123]

Thanq Bro.:)

[jagan]

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.