Join 1:n

[andrew001]

hi

I've two table that I joined with left join (1.N)

I'd like to use the Result_table in a pivot table: if I SUM with the MOV column is OK, but I don't know how I have to do with SALDO column, because I need the only  first value by CONTO_KEY

Thank you

TAB_SALDI

CONTO_KEY    OWNER SALDO

10001EUR         XYZ       10000

TAB_MOV

CONTO_KEY    OWNER  MOV

10001EUR         XYZ       200

10001EUR         XYZ       300

10001EUR         XYZ       500

RESULT_TABLE

CONTO_KEY    OWNER SALDO   MOV

10001EUR         XYZ       10000     200

10001EUR         XYZ       10000     300

10001EUR         XYZ       10000      500

[CELAMBARASAN · Accepted solution]

Then check with it

TAB_SALDI:

Load *,CONTO_KEY&'1' AS Key Inline

[

CONTO_KEY,OWNER,SALDO

10001EUR,XYZ,10000

10002EUR,XYZ,20000

];

join

LOAD *,if(Previous(CONTO_KEY)<>Previous(CONTO_KEY),CONTO_KEY&'1',CONTO_KEY&ID) AS Key;

Load * Inline

[

CONTO_KEY,ID,OWNER,MOV

10001EUR,1,XYZ,200

10001EUR,2,XYZ,300

10001EUR,3,XYZ,500

10002EUR,1,XYZ,200

10002EUR,2,XYZ,300

10002EUR,3,XYZ,500

];

Note: conto_key field must be in a order