aggregation

[AmirKilkr]

Hello,

Can you assist me in understanding the problem?

I need you to use two tables (left join) and then have one aggregate table but I cant do it.

SalesNew:
LOAD
ItemID,
"Date",
CampaignID,
DocumentNumber,
StoreHanut,
TotalPriceBeforVat,
"Time",
SalesmanID,
Quantity
FROM [lib://DBF/SalesNew.qvd]
(qvd);
Left Join
LOAD
"Category ID",
"Category Name"
FROM [lib://DBF/Category.qvd]
(qvd);

SalesAgg:
LOAD
Year("Date")&Num(Month("Date"),00) as YearMonth,
"Category ID",
Sum(Quantity) as Quantity,
Sum(TotalPriceBeforVat ) as TotalPriceBeforVat
Resident SalesNew
Group By
Year("Date")&Num(Month("Date"),00),
"Category ID";
Store * From SalesAgg Into [lib://DBF/SalesAgg.qvd];
Drop Table SalesAgg;

[Thiago_Justen_ · Accepted solution]

Hi there,

What about this?

Map_Category:

Mapping
LOAD
"Category ID",
"Category Name"
FROM [lib://DBF/Category.qvd]
(qvd);

SalesNew:
LOAD
ItemID,
"Date",

Year("Date")&Num(Month("Date"),00) as YearMonth,

"Category ID",

ApplyMap('Map_Category',[Category ID], Null())      As "Category Name",
CampaignID,
DocumentNumber,
StoreHanut,
TotalPriceBeforVat,
"Time",
SalesmanID,
Quantity
FROM [lib://DBF/SalesNew.qvd](qvd);

Store SalesNew Into [lib://DBF/SalesNew.qvd](qvd);

SalesAgg:
LOAD

YearMonth,
"Category ID",
Sum(Quantity) as Quantity,
Sum(TotalPriceBeforVat ) as TotalPriceBeforVat
Resident SalesNew
Group By
YearMonth,
"Category ID";

Store SalesAgg Into [lib://DBF/SalesAgg.qvd](qvd);
Drop Tables SalesAgg, SalesNew;

Hope it helps you!