It's possible to divide amount by identical occurence

[Anonymous]

Hello,

In my PDF /Screenshot, you will see what i try to do.




I tried to do this in SQL but unsuccessfully.
So i tried to divide "amount" per "nb_occurrence", but i have a problem with round : sometimes i have "3739.998" or "3738" or "3741" but never "3740"

In Eclispe, i did this :

import java.math.BigDecimal;
import java.math.RoundingMode;
public class div {
    public static void main(String[] args) {
        BigDecimal a, b;
        BigDecimal reste, result;

            Scanner scan = new Scanner(System.in);
        a = BigDecimal.valueOf(scan.nextInt());
        b = BigDecimal.valueOf(scan.nextInt());
        result = a.divide(b, 3, RoundingMode.HALF_DOWN);
        reste = a.subtract(result);        
        do {    
                b = b.subtract(new BigDecimal(1));
                result = reste.divide(b, 3, RoundingMode.HALF_DOWN);
                System.out.println("Résulat = " + result);
                reste = reste.subtract(result);            
                System.out.println("reste = " + reste);
                System.out.println("b = " + b+"\n");
        
        } while (reste.compareTo(BigDecimal.ZERO) != 0);
    }
}

and i tried to apply in TjavaRow/TjavaFlex :unsuccessfully

My Job is designed :

TpostgresqlInput ----row1---->TjavaRow----row2---->Tmap----row3--->TpostgreslOuput

I dont know a lot about Java...

So Can you help me ?

Regards,

Talend.pdf_20161207-0149.pdf

[Anonymous]

Hi  
First, you can use tAggregateRow to populate the avg value by group, then, read the source file again and do a join with the previous result to map the amount value, the job looks like:
tPostgresqlInput--main--tAggregateRow--main--tHashOutput
   |
onsubjobok
   |
tPostGresqlInput--main--tMap.....
                                       |
                                   tHashInput

tHashOutput: store the result to memory for used later.
tHashInput: read the result from memory.

Regards
Shong

[Anonymous]

Hi Shong,
Thank you for your answer.
i tried, but without success.
In my component tAggregateRow :
first attempt :
Group by : numero_commande / reference_commande / produit
operation : amount -> avg
My result is :
amount - numero_commande -  reference_commande - produit
3740     -  COM20161206 - REF20161206_001 - Produit_A
3740     -  COM20161206 - REF20161206_001 - Produit_B
3740     -  COM20161206 - REF20161206_001 - Produit_C
2nd attempt :
Group by : numero_commande / reference_commande
operation : amount -> avg
My result is :
amount - numero_commande -  reference_commande
3740     -  COM20161206 - REF20161206_001

Regards,

[Anonymous]

Hi jp 

Sorry, I think I have misunderstand your requirement before, you should use the count operation to count the number of each group elements, and then use  amount column divided the count number on next tMap component, eg:
[font=Verdana, Helvetica, Arial, sans-serif] input-main--tAggregateRow--main--tMap...[/font]


[font=Verdana, Helvetica, Arial, sans-serif] tAggregateRow: [/font]
[font=Verdana, Helvetica, Arial, sans-serif] output columns:[/font]
amount-->operation:first
count_number-->operation: count
numero_commande-->group element

reference_commande-->group element

produit-->operation: first




Regards
Shong

[Anonymous]

Hello,

my result is :
amount    - numero_commande -  reference_commande - produit
1246.666 -  COM20161206 - REF20161206_001 - Produit_A
1246.666 -  COM20161206 - REF20161206_001 - Produit_B
1246.666 -  COM20161206 - REF20161206_001 - Produit_C
as you can see sum not corresponds : 1246.666 + 1246.666 + 1246.666 = 3739.998 so it's different to 3740
Regards,

[Anonymous]

Hi jp 
You are doing division, not subtraction, the result is an avg value.

Regards
Shong