help please to remove group by

[#1]

here I dont want to group by Day(TRA_DATE). can i do that? because it makes unnecessary more rows in my table. please help

main2:

LOAD SALCAT as Category,

     SALRUTID as Loc,

     SUM(SALTGQTY) as Tar_qty,

     SALYEAR as year,

     SALMON as mon

FROM

(qvd) WHERE SALYEAR>='2013' Group By SALCAT,SALRUTID,SALYEAR,SALMON;

Inner join

LOAD TRA_CAT as Category ,

     TRA_LOC as Loc,

     SUM(TRA_QTY) as cur_stock

FROM

(qvd) Group By TRA_CAT,TRA_LOC;

Inner Join

LOAD

     TRA_LOC as Loc,

     year(TRA_DATE) as year,

     Month(TRA_DATE) as mon,

     SUM(TRA_QTY) as tar_sale ,

     Round( SUM(TRA_QTY)/30) as per_day_sale ,

     Count( Month(TRA_DATE)) as daycount,

     TRA_CAT as Category,

     Day(TRA_DATE) as date

FROM

(qvd) Group By TRA_CAT,TRA_LOC, year(TRA_DATE),Month(TRA_DATE),Day(TRA_DATE);

Inner Join

LOAD SITE,

     SITENAM,

     SITECAT,

     SITTETOWN,

     ROUTID as Loc,

     DEPOT

FROM

(qvd);

Inner Join

LOAD

     //TRA_LOC as Loc,

     TRA_CAT as Category,

     SUM(TRA_QTY) as totsale,

     Month(TRA_DATE) as mon

FROM

(qvd) Group By TRA_CAT, Month(TRA_DATE);

STORE main2 into main2.qvd;

DROP Table main2;

/////////////////////////////////////////////////////////////////

main_final:

LOAD Loc,

     Category,

     year,

     mon,

     Tar_qty,

     cur_stock,

     cur_stock-Tar_qty as Balance,

     Round( Tar_qty/30,0.1) as perDayTar,

     tar_sale,

     per_day_sale,

      Round((tar_sale/totsale)*100,0.1)&'%' as sale_perc,

     //daycount,

    // SITE,

     //SITENAM,

//   date,

     if(date=1,tar_sale) as day1,

     if(date=2,tar_sale) as day2,

     if(date=3,tar_sale) as day3,

     if(date=4,tar_sale) as day4,

     if(date=5,tar_sale) as day5,

     if(date=6,tar_sale) as day6,

     if(date=7,tar_sale) as day7,

     if(date=8,tar_sale) as day8,

     if(date=9,tar_sale) as day9,

     if(date=10,tar_sale) as day10,

     if(date=11,tar_sale) as day11,

     if(date=12,tar_sale) as day12,

     if(date=13,tar_sale) as day13,

     if(date=14,tar_sale) as day14,

     if(date=15,tar_sale) as day15,

     if(date=16,tar_sale) as day16,

     if(date=17,tar_sale) as day17,

     if(date=18,tar_sale) as day18,

     if(date=19,tar_sale) as day19,

     if(date=20,tar_sale) as day20,

     if(date=21,tar_sale) as day21,

     if(date=22,tar_sale) as day22,

     if(date=23,tar_sale) as day23,

     if(date=24,tar_sale) as day24,

     if(date=25,tar_sale) as day25,

     if(date=26,tar_sale) as day26,

     if(date=27,tar_sale) as day27,

     if(date=28,tar_sale) as day28,

     if(date=29,tar_sale) as day29,

     if(date=30,tar_sale) as day30,

     if(date=31,tar_sale) as day31

FROM

(qvd) ;

Inner Join

LOAD ITPCAT as Category,

     OSTLOC,

     QTY

FROM

(qvd) ;

STORE main_final into main_final.qvd;

DROP Table main_final;

////////////////////////////////////////////////////////////////////////////////////////////

LOAD Category,

     Loc,

     year,

     mon,

     Tar_qty,

     cur_stock,

     Balance,

     perDayTar,

     tar_sale,

     per_day_sale,

     sale_perc,

     day1,

     day2,

     day3,

     day4,

     day5,

     day6,

     day7,

     day8,

     day9,

     day10,

     day11,

     day12,

     day13,

     day14,

     day15,

     day16,

     day17,

     day18,

     day19,

     day20,

     day21,

     day22,

     day23,

     day24,

     day25,

     day26,

     day27,

     day28,

     day29,

     day30,

     day31,

     OSTLOC,

     QTY

FROM

(qvd);

[#2]

hi friends, please help me

[#3]

Dont need to use the aggregate function(Sum and AVG) in a script, we can do this in chart's expression. So don;t need to use the group by function at any where.

Note: If we use the aggregate function in script, then it would affect the performance.

Regards,

Rajdeep G. 

[#4]

hi Kabilan, my problem is i should get the day(TRA_date) and then it affect to the GROUP BY.

LOAD

     TRA_LOC as Loc,

     year(TRA_DATE) as year,

     Month(TRA_DATE) as mon,

     SUM(TRA_QTY) as tar_sale ,

     Round( SUM(TRA_QTY)/30) as per_day_sale ,

     Count( Month(TRA_DATE)) as daycount,

     TRA_CAT as Category,

     Day(TRA_DATE) as date

FROM

(qvd) Group By TRA_CAT,TRA_LOC, year(TRA_DATE),Month(TRA_DATE),Day(TRA_DATE);

how can i remove it from GROUP BY?

[Anonymous]

hi dus,

try this method

LOAD

     TRA_LOC as Loc,

     year(TRA_DATE) as year,

     Month(TRA_DATE) as mon,

     SUM(TRA_QTY) as tar_sale ,

     Round( SUM(TRA_QTY)/30) as per_day_sale ,

     Count( Month(TRA_DATE)) as daycount,

     TRA_CAT as Category

FROM

(qvd) Group By TRA_CAT,TRA_LOC, year(TRA_DATE),Month(TRA_DATE);

LOAD

     TRA_CAT as Category,

     Day(TRA_DATE) as date

FROM

(qvd);

Is that what you need?

Regards,