Finding match in same table

[Anonymous]

Hello,

I have a table with following columns:

KEY	CODE	TRANS
1	FG	10
1	CO	12
1	FI	21
2	FG	23
2	FI	21
3	FG	43
3	CO	2

As you can see the KEY has some duplication values, i need a script to check, if the value of column CODE is FG and with the same KEY if CODE's value is CO then Trans of FG minus Trans of CO. for example when key is 1 then 10-12 or when key is 3 then 43-2 .

[maxgro · Accepted solution]

this?

source:

LOAD KEY,

     CODE,

     TRANS

FROM

[https://community.qlik.com/thread/159441]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (source)

load KEY, CODE as CODE2, TRANS as TRANS2 Resident source Where CODE = 'CO';

final:

NoConcatenate load

  KEY, CODE, if(CODE='FG'and CODE2='CO', TRANS - TRANS2, TRANS) as TRANS

Resident source;

DROP Table source;

[Anonymous]

Thank you massimo, i really appreciate your help and correct answer.