Create a tag if records are the same, Date and Value

[Paula1]

Cod	Data	Seq	Tipo_Mov.	Valor
7898	09/10/2023	147587	VND	36,72
7898	09/10/2023	147587	VND	89,1
7898	09/10/2023	147587	VND	186,3
7898	24/11/2023	150747	VND	159,
7898	24/11/2023	150748	DEV	-159,
7898	29/11/2023	151223	VND	176,
7898	29/11/2023	151224	DEV	-176,
11967	29/11/2023	161185	VND	119,2
119647	29/11/2023	151187	VND	118,15
119647	01/12/2023	151466	DEV	243,
119647	01/12/2023	151467	VND	-243,

If Cod, have a line Type_Mov VND and DEV on the same Date and as the month value, create a tag with the name DEV.

The record must have Code, Date and Type_Mov, line with DEV and VND. same as the example below

Cod	Data	Seq	Tipo_Mov.	Valor	Tag
7898	09/10/2023	147587	VND	36,72
7898	09/10/2023	147587	VND	89,1
7898	09/10/2023	147587	VND	186,3
7898	24/11/2023	150747	VND	159,	Dev
7898	24/11/2023	150748	DEV	-159,	Dev
7898	29/11/2023	151223	VND	176,	Dev
7898	29/11/2023	151224	DEV	-176,	Dev
11967	29/11/2023	161185	VND	119,2
119647	29/11/2023	151187	VND	118,15
119647	01/12/2023	151466	DEV	243,	Dev
119647	01/12/2023	151467	VND	-243,	Dev

[Paula1]

Santos:
LOAD
Cod,
Data,
Seq,
Tipo_Mov,
Valor,
If(Tipo_Mov = 'DEV', 'Dev', '') as TagTemp
FROM
[C:\Users\vasco\Desktop\TMP2\DEMO1.xls]
(biff, embedded labels, table is Plan1$);

// Criar uma tabela temporária para armazenar os valores de VND e DEV
TempTable:
LOAD
Cod,
Data,
Valor,
Seq,
If(Tipo_Mov = 'VND', Valor, -Valor) as ValorAbsoluto,
TagTemp
RESIDENT Santos;

// Associar as transações de VND e DEV com os mesmos códigos, datas e valores opostos
LEFT JOIN (TempTable)
LOAD
Cod,
Data,
ValorAbsoluto,
'Dev' as Tag
RESIDENT TempTable
WHERE TagTemp = 'Dev';

// Atualizar a tabela original com as tags
LEFT JOIN (Santos)
LOAD
Cod,
Data,
Seq,
Tag
RESIDENT TempTable
WHERE Tag = 'Dev';

DROP TABLE TempTable;

[Ahidhar · Accepted solution]

you could also try like this

tab:
load * Inline
[
Cod, Date, Seq, Movement_Type, Valor
7898, 09/10/2023, 147587, VND, 3672
7898, 09/10/2023, 147587, VND, 891
7898, 09/10/2023, 147587, VND, 1863
7898, 24/11/2023, 150747, VND, 159
7898, 24/11/2023, 150748, DEV, -159
7898, 29/11/2023, 151223, VND, 176
7898, 29/11/2023, 151224, DEV, -176
11967, 29/11/2023, 161185, VND, 1192
119647, 29/11/2023, 151187, VND, 11815
119647 ,01/12/2023,151466, DEV, 243
119647, 01/12/2023, 151467, VND, -243
];
left join(tab)
tab1:
load
Cod,Date,Movement_Type as Tag
resident tab where Movement_Type='DEV';