on 10-21-2015 2:55 PM
Hi Guys,
I have a DSO1 with Key field as MATNR and Inspection lot no. , as per the requirement I have calculated Average lot quantity per Material using a routine. The Avg. lot quantity is populating in every material as shown in the below image. The only field which makes the record unique is Inspection lot number (PRUEFLOS), Average Does not depend on year also.
Now I am populating this data in DSO2 with key field as MATNR as i want single MATNR corresponding to an single Average lot Quantity.But my output is coming as shown below:
What is happening is Avg. lot Qty is getting summed up in the result.
Please tell me what i should do so that the Avg lot Qty doesnt add up in the DSO and just shows me one correct value as shown in DSO1.
Regards,
Pulkit
Hi Pulkit
Please paste the code that you have written to calculate the average lot qty.
Also give the output of 2012 in the second DSO to check how the source data has changed based on your routine code. (the screenshot that is provided now has 2013 as year and it doesn't sum up the four entries in your source)
Regards
Karthik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi kartik,
Thanks for the prompt reply. The Code is working fine and DSO1 is working as expected.
The year 2012 doesnt appear in second DSO but it doesnt matter as I can remove it, the only field i want is MATNR,MATKL and Avg Lot quantity.
As per my observation what is happening is if there are 10 line item of MATNR and avg is 100 in DSO 1, then in DSO 2 the Same MATNRs Avg is coming as 100*10 =1000 .
Could you help.
Regards,
Pulkit
In New table and change log table, give the material 1040013R alone in selection and get the output.
Share the screenshot with record mode as you did earlier.
Hope you have already checked in the transformation from first DSO to second DSO where the avg lot qty keyfigure is direct mapping with overwrite mode. (not summation)
If you haven't check this, please do and make sure its in place.
Regards
Karthik
Hi Pulkit,
this result is likely expected as per you design. a record/transaction gets overwritten only when the key fields are same, however when a set of records (from your example when you load DSO2 without line item), the key figure gets summarized (even when the mappings are set as overwritten).
Let me explain.
material(a1234) with line item 1 and quantity 10 , if you have two transactions with same values coming from source (it is possible in case of full load/from flat file ) and when you run DTP first time, these two records get summarized(unless you are deleting duplicate records) when you load them either to a cube or to a DSO.
to overcome this issue, you can write code in start routine and by counting the no of line items against each material and pass it to variable, now call this variable in end routine and do a simple computation like total value/no.of line items. this will give you exact average values as in DSO1.
Hi Pulkit
This is not the screenshot that I am expecting. I thought of having a look at your new and change log table for that material in your second DSO.
Please check what Jyothi has explained as well. But in your case, I think the line item is different in each case with the field PRUEFLOS.
Regards
Karthik
Yes I know, have mentioned that in my original post that PRUEFLOS is the only object which makes the a record unique. My requirement is to give average per material with only one line item and for that reason i have removed PRUEFLOS in my DSO 2.
New data table in DSO 2 has 0 records and i have attached change log table pic in the above post.
Is there any solution without using routine ?
Regards,
Pulkit
Hi Pulkit
I have created the same scenario in my sandbox system and it works as Jyothi has mentioned. Though you have material and the other field in your first DSO, when you look up the data based on material, it will sum up all the line items for that material.
There is no much way except writing a routine as you did earlier in the first DSO.
Rather than calculating average which you have already done in the first DSO, you could just delete the adjacent duplicates based on material.
Deleting adjacent duplicates is simple and no much code required for it. Try the below code in your start routine
SORT SOURCE_PACKAGE BY MATNR.
DELETE ADJACENT DUPLICATES FROM SOURCE_PACKAGE COMPARING MATNR.
Try this and let us know if it worked.
Regards
Karthik
Hi Pulkit,
you should use the delete adjacent in DSO2 start routine, in this way when your run DTP(DSO1-->DSO2), it should delete all duplicate.
if it doesn't work, then try semantic group (this will group the data with same keys in one data package) from DTP and tick the check box of MATNR and try running DTP again.
Message was edited by: Jyothi velpula
hi
in the second dso you can create a key figure not cumulative and map it on the dso1 key figure
hope it helps
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.