how to get sales and target by product category and product id

[sharmaKavita]

// Target Table
target:
LOAD * INLINE [
productcategory, target, dates
Electronics, 100000.00, '01/02/2021'
Clothing, 50000.00, '01/01/2023'
'Home Appliances', 75000.00, '01/01/2022'
];
Store target into [lib://qv/Atarget.qvd](qvd);
// Sales Table
sales:
LOAD * INLINE [
productid, sales, dates
1, 75000.00, '01/01/2022'
1, 75000.00, '01/04/2023'
2, 40000.00, '01/01/2022'
1, 75000.00, '01/02/2023'
3, 80000.00, '01/01/2022'
3, 80000.00, '01/01/2022'
];
Store sales into [lib://qv/Asales.qvd](qvd);

// Product Table
product:
LOAD * INLINE [
productid, productcategory, productname
1, 'Electronics', 'Smartphone'
2,'Electronics', 'tv'
2, 'Clothing', 'T-Shirt'
3, 'Home Appliances', 'Refrigerator'
];
Store product into [lib://qv/Aproduct.qvd](qvd);
exit Script;

[udit_k · Accepted solution]

Firstly  there should be unique product id record in product table, we could see there  are duplicate product _id:-

IF this is corrected , we can use Product table as a mapping table to get ID in Target table by using applymap.

We could now associate Target and Sales table using ProductID and Date Key , and get data as per required.