Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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';
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;
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';