Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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!
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!