Calculate measure based on join

[chetansehgal]

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

[adityaakshaya]

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  

[chetansehgal]

Hi Akshaya,

Thanks for helping. You missed the attachment here. could you plz attach the qvw.

Thanks and Regard,

Chetan

[vvvvvvizard]

Try load Distinct in both tables

and also use left join

[adityaakshaya]

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