topic Re: Finding match in same table in QlikView

Finding match in same table

Anonymous — Thu, 09 Apr 2015 10:27:01 GMT

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 .

Re: Finding match in same table

maxgro — Thu, 09 Apr 2015 19:54:28 GMT

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;

Re: Finding match in same table

Anonymous — Fri, 10 Apr 2015 03:22:33 GMT

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