topic Re: Calculate measure based on join in QlikView

Calculate measure based on join

chetansehgal — Sun, 03 Jun 2018 10:25:02 GMT

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

Re: Calculate measure based on join

adityaakshaya — Sun, 03 Jun 2018 14:58:32 GMT

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

Re: Calculate measure based on join

chetansehgal — Wed, 06 Jun 2018 12:16:08 GMT

Hi Akshaya,

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

Thanks and Regard,

Chetan

Re: Calculate measure based on join

vvvvvvizard — Wed, 06 Jun 2018 12:24:50 GMT

Try load Distinct in both tables

and also use left join

Re: Calculate measure based on join

adityaakshaya — Wed, 06 Jun 2018 16:19:52 GMT

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