on 02-23-2013 2:46 AM
Hello Guru's
My requirement is, I need sum the NETVAL field based on item number(CCNCU) and country origin(CUCOO) fields combination. I did the mapping like below, But Looks like SortByKey node function (in the summation of net values) is not working properly in my case. Amounts values are not populated as expected.
See the below screens how the data is jumbled in SortByKey Node function.If you see the queue values in shortByKey (in0,in1 and out)
Especially for the values 89040031US(146.15) and 89040031MY(2120.00) values got interchanged(I have highlighted in pink and blue color). That is the reason why my output is wrong.
Can you pls suggest the solution, Some coding/graphical representation will be helpful.
Appreciate your help.
Thanks
Arun
Hello Arun,
The reason why your output got jumbled is because you implemented sorting on the Netval using keys concatenated from CCNCU+CUCOO but did not implement the same sorting before the formatByExample function. If you would also sort the concatenated value, then your output will be correct.
Hope this helps,
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Arun,
Could you please try this advanced UDF of type "context"
public void calculateNetval(String ccuncu[],String cucoo[],String netval[],ResultList result,Container container)
{
String concat[]=new String[ccuncu.length];
boolean status[]=new boolean[ccuncu.length];
int i;
for(i=0;i<ccuncu.length;++i)
{
status[i]=false;
concat[i]=ccuncu[i]+cucoo[i];
}
int j;
double sum=0;;
for(i=0;i<ccuncu.length;++i)
{
if(status[i])
{
continue;
}
sum=0;
for(j=i;j<ccuncu.length;++j)
{
if(status[j])
{
continue;
}
if(concat[j].equals(concat[i]))
{
sum=sum+new Double(netval[j]).doubleValue();
status[j]=true;
}
}
result.addValue(sum);
}
}
input to the UDF are as follows
String ccuncu[]={"890400301","890400301","890400301","890400301","890400301"};
String cucoo[]={"IE","US","SN","MY","US"};
String netval[]={"415.54","146.15","225.94","2120.09","100.00"};
Output(ItemFOBF as incidated in the image you uploaded)
String netvalSum={415.54,246.15,225.94,2120.09}
__________________________________________________________________________________________________________________________
In UDF String concat[] ={890400301IE,890400301US,890400301SN,890400301MY}; this is because "US" has occurred twice in String cucoo[]; thus netval value (146.15+100.00) for cucoo[i]="US".
Hope this resolves the problem.
Regards
Anupam
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Anupam,
The logic of Calculation is working fine now. But the main problem is the values in which the order the Item codes are displaying.
Here is my target structure.
- <Item>
<ItemHsCode>89040031</ItemHsCode>
<ItemDesc>MANUALLY ADDED HTS CODE DESCRIPTION</ItemDesc>
<ItemCtryOrigin>IE</ItemCtryOrigin>
<ItemQty>1.0</ItemQty>
<ItemUnit>LOT</ItemUnit>
<ItemInHAWB>TESTFEB20#01</ItemInHAWB>
- <ItemAdditionalInfo>
<ItemFOBForeignAmt>415.54</ItemFOBForeignAmt>
<ItemFOBForeignCurr>TWD</ItemFOBForeignCurr>
</ItemAdditionalInfo>
</Item>
If you observe the above. First I need to populate the ItemHsCode(CCNUCU), ItemCtryOrigin(CUCOO) and then then I need to calculate NETVAL in the field ItemFOBForeignAmt.
All the source fields are in one segment. So What I am doing here is I am mapping the Item(CCNUC) and country(CUCOO) from segments source fields through below mapping.
So the calculation for the combination of CCNCU and CUCOO is working but the item, ItemCtryOrigin are populating in different order due to mapping.( I hope you got my point, Mapping and JAVA logic are working differently)
Appreciate your help.
Thanks
Arun
Hi Friends,
Any help would be appreciated.
Thanks
Arun
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Arun,
Using sortybykey fn ,you are sorting NEVTAL based on second i/p (just keep in mind that ,here you are not sorting 2nd input). Because of this when you click on display queue u see NEVATL sorted based on 2nd i/p ,where as 2nd i/p remains as it is.
So in order to sort the 2nd i/p i suggested you to use sort fn b/w concat and splibyvalue(value change).
Don't get confuse by opening display queue at sortbykey fn.open display queue at format by example.
You can see as you are expecting.
By the way what is your exact requirement ,from your screen you are trying to sum up the NEVTAL values for each country.say sum of ALL US NEVTAL values as one o/p .
This is simple logic no need of any udf.If you are still facing any issue.Can you show ur display queue results.
Regards
Venkat
Hi Venkat,
Here is my exact requirement.
The logic of Calculation is working fine now. But the main problem is the values in which the order the Item codes are displaying.
Here is my target structure.
- <Item>
<ItemHsCode>89040031</ItemHsCode>
<ItemDesc>MANUALLY ADDED HTS CODE DESCRIPTION</ItemDesc>
<ItemCtryOrigin>IE</ItemCtryOrigin>
<ItemQty>1.0</ItemQty>
<ItemUnit>LOT</ItemUnit>
<ItemInHAWB>TESTFEB20#01</ItemInHAWB>
- <ItemAdditionalInfo>
<ItemFOBForeignAmt>415.54</ItemFOBForeignAmt>
<ItemFOBForeignCurr>TWD</ItemFOBForeignCurr>
</ItemAdditionalInfo>
</Item>
If you observe the above. First I need to populate the ItemHsCode(CCNUCU), ItemCtryOrigin(CUCOO) and then then I need to calculate NETVAL in the field ItemFOBForeignAmt.
All the source fields are in one segment. So What I am doing here is I am mapping the Item(CCNUC) and country(CUCOO) from segments source fields through below mapping.
So the calculation for the combination of CCNCU and CUCOO is working but the item, ItemCtryOrigin are populating in different order due to mapping.( I hope you got my point, Mapping and JAVA logic are working differently)
Appreciate your help.
Thanks
Arun
Hi,
Try the below easy method.
CCNCU--->contact(with any delimiter)-->sort-->splitbyvalue(valuechange)-->
CUCOO---/
collapsecontext-->splitbyvalue(eachvalue)-->split both the strings by using udf or by using
index and substring std fns and pass the values to ItemHsCode and ItemCtryOrigin .
Map target field item to collapsecontext o/p
Cheers
Venkat
Hello,
Did you notice the pattern in which these got jumbled??These values are appearing as per the alphabetical order of the concatenated values...Eg if u arrange 890400301IE, 890400301US,890400301SN,890400301MY alphabetically then it will come as 890400301IE,890400301MY,890400301SN,890400301US...You values have got jumbled in exactly this order...If you double click on sortByKey you will find three options of sorting..Lexicographically,Lexicographically(Case insensitive) & Numeric..
See how sortBykey will change values(with lexicographically):
Anyways for your mapping I don't think there is any need of having a sortByKey for summing the fields. Look in the example below, I wanted to sum APPL_KEY as per the changing values of CHNUM.which I was able to do with format example only, if I apply sum after format example.
Try with the above..And sorry if I misunderstood your requirement..
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 | |
6 | |
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.