Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a scenario in which i have 2 tables one table TableA and another is Sales.
I want to apply a formula which is having measures from both the tables. But i want to take only those records from Sales which are present in the TableA. I cant use left outer join here because there is many to many relationship between to tables which will create duplicate records. I have tried where exists as well but it takes too long to load the data.
TableA -->
Customer | Material | OOS |
1 | A | 10 |
1 | B | 20 |
1 | C | 30 |
2 | A | 10 |
2 | B | 30 |
Sales Table -->
Customer | Material | Date | Sales |
1 | A | 01-10-2018 | 200 |
1 | B | 01-10-2018 | 300 |
1 | C | 01-10-2018 | 500 |
1 | D | 01-10-2018 | 600 |
1 | E | 01-10-2018 | 200 |
2 | A | 01-10-2018 | 400 |
2 | B | 01-10-2018 | 500 |
2 | C | 01-10-2018 | 700 |
2 | D | 01-10-2018 | 600 |
1 | A | 02-10-2018 | 2000 |
1 | B | 02-10-2018 | 3000 |
1 | C | 02-10-2018 | 5000 |
1 | D | 02-10-2018 | 6000 |
1 | E | 02-10-2018 | 4000 |
2 | A | 02-10-2018 | 5000 |
2 | B | 02-10-2018 | 3000 |
2 | C | 02-10-2018 | 8000 |
2 | D | 02-10-2018 | 2000 |
Logic to calculate the measure---> Here i have taken only those records from Sales tables for which combination Customer and Material are present in TableA.
Customer | Material | OOS | Calculated Measure formula | Calculated Measure output |
1 | A | 10 | 10*(200+2000) | 4000 |
1 | B | 20 | 20*(300+3000) | 66000 |
1 | C | 30 | 30*(500+5000) | 165000 |
2 | A | 10 | 10*(400+5000) | 54000 |
2 | B | 30 | 30*(500+3000) | 105000 |
Measure= | Calculated Measure output/5 | 78800 |
Any help on the above will be appreciated.
Thanks,
Chetan
Hi Chetan,
Please follow the below steps after loading Table A and Sales table to get your desired output
1. Roll- up Sales Tables
2. Do the left outer join
3. Calculate the Measures
Attached is the QVW files for your reference. I have Calculated the measure at script level.
TableA:
LOAD *,Customer&Material as %KeyCustomerMaterial INLINE [
Customer, Material, OOS
1, A, 10
1, B, 20
1, C, 30
2, A, 10
2, B, 30
];
Sales:
LOAD *,Customer&Material as %KeyCustomerMaterial INLINE [
Customer, Material, Date, Sales
1, A, 01-10-2018, 200
1, B, 01-10-2018, 300
1, C, 01-10-2018, 500
1, D, 01-10-2018, 600
1, E, 01-10-2018, 200
2, A, 01-10-2018, 400
2, B, 01-10-2018, 500
2, C, 01-10-2018, 700
2, D, 01-10-2018, 600
1, A, 02-10-2018, 2000
1, B, 02-10-2018, 3000
1, C, 02-10-2018, 5000
1, D, 02-10-2018, 6000
1, E, 02-10-2018, 4000
2, A, 02-10-2018, 5000
2, B, 02-10-2018, 3000
2, C, 02-10-2018, 8000
2, D, 02-10-2018, 2000
];
DROP Fields Customer,Material from Sales;
Left Join(TableA)
Load %KeyCustomerMaterial, Sum(Sales) as TotalSales Resident Sales Group by %KeyCustomerMaterial;
NoConcatenate
TableA_Final:
LOAD *,TotalSales*OOS as CalculatedMeasureOutput Resident TableA;
DROP Table TableA;
Regards,
Akshaya
Hi Akshaya,
Thanks for helping. You missed the attachment here. could you plz attach the qvw.
Thanks and Regard,
Chetan
Try load Distinct in both tables
and also use left join
Hi Chetan,
Attachment is showing to me. I don't know whats went wrong. You can copy the code and put it in your script editor. It will work for you.
I am reattaching the fie for you.
Regards,
Akshaya