1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
| Table1:
LOAD *,
AutoNumber( ACTION &DATE &montant & reference) as Key;
LOAD
RecNo()as id,ACTION,mcode,DATE,montant,reference,message
FROM [lib://Documents/données.CSV]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq)
Where [mcode] <>'1913' and [mcode]<>'2001' ;
NoConcatenate
Table2:
Load*
Resident Table1 order by [DATE];
drop table Table1;
Outer Join(Table2)
LOAD
Key,
max(id) as MaxId
RESIDENT Table2
GROUP BY Key;
FinalTable:
Load*,
If(((id = MaxId) and ([message] = 'ERROR'OR [message] = 'ACCEPTED' or [message] = 'REFUSED' or [message] = 'CANCELLED'))or([message] = 'ACCEPTED') , 1, 0) as DB
Resident Table2
Order By id ;
drop Table Table2; |
Partager