help please to remove group by

Thu, 12 Dec 2013 12:43:46 GMT

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);

Re: help please to remove group by

Fri, 13 Dec 2013 06:14:25 GMT

hi friends, please help me

Re: help please to remove group by

Fri, 13 Dec 2013 06:32:30 GMT

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.

Re: help please to remove group by

Fri, 13 Dec 2013 06:37:45 GMT

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?

Re: help please to remove group by

Anonymous — Fri, 13 Dec 2013 07:00:39 GMT

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,

topic Re: help please to remove group by in QlikView

help please to remove group by

Re: help please to remove group by

Re: help please to remove group by

Re: help please to remove group by

Re: help please to remove group by