Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(qvd) Group By TRA_CAT,TRA_LOC;
Inner Join
LOAD
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
(qvd) Group By TRA_CAT,TRA_LOC, year(TRA_DATE),Month(TRA_DATE),Day(TRA_DATE);
LOAD SITE,
SITENAM,
SITECAT,
SITTETOWN,
ROUTID as Loc,
DEPOT
(qvd);
//TRA_LOC as Loc,
SUM(TRA_QTY) as totsale,
Month(TRA_DATE) as mon
(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
(qvd) ;
LOAD ITPCAT as Category,
OSTLOC,
QTY
STORE main_final into main_final.qvd;
DROP Table main_final;
////////////////////////////////////////////////////////////////////////////////////////////
LOAD Category,
Loc,
Balance,
perDayTar,
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,
hi friends, please help me
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.
hi Kabilan, my problem is i should get the day(TRA_date) and then it affect to the GROUP BY.
how can i remove it from GROUP BY?
hi dus,
try this method
TRA_CAT as Category
(qvd) Group By TRA_CAT,TRA_LOC, year(TRA_DATE),Month(TRA_DATE);
Is that what you need?
