Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Re: Count and Sum in Script execution

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
Contributor II

Re: Count and Sum in Script execution

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

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;

Highlighted
markgraham123
Contributor II

Re: Count and Sum in Script execution

Thanq verymuch bro.

markgraham123
Contributor II

Re: Count and Sum in Script execution

settu.periyasamysunindia

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

MVP
MVP

Re: Count and Sum in Script execution

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

markgraham123
Contributor II

Re: Count and Sum in Script execution

Thanq my friend...Smiley Happy

Geek is back.

Re: Count and Sum in Script execution

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
Contributor II

Re: Count and Sum in Script execution

Thanq Bro.Smiley Happy

MVP
MVP

Re: Count and Sum in Script execution

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.