Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This mess works:
LOAD
"Date",
if(Index(Description, 'CHECKCARD') = 1,
if(FindOneOf(Right(Description,Len(Description)-15),'0123456789') > 1,
Left(Right(Description,Len(Description)-15), FindOneOf(Right(Description,Len(Description)-15),'0123456789')-1),
Right(Description,Len(Description)-15)),
Description)
As Description,
Money(-1 * Amount) As Amount
FROM [lib://Downloads/stmt (3).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)
Where Num(Amount) < 0
but what I'd really like is a way to get an expression block in the field list. Something like:
LOAD
"Date",
Magic = {
set x=2;
set y=2;
x+y;
},
Amount
Is there a way to do that? If not can you enhance the Data Load Editor.
Thanks.
Or:
LOAD
"Date",
Begin
set x=2;
set y=2;
(x+y) As Magic;
End
},
Amount
I don't believe that something in this way is possible but I think it could be simplified. Depending of the field-content and your checking other string-functions like subfield/textbetween/keepchar might be more suitable as your used left/right-approach. If not could in each case a Preceding Load be useful to simplify the logic, maybe in this way:
LOAD *, if(Y, if(Z, Left(Right(Description,X), Z-1, Description) as DescriptionNew;
LOAD *, FindOneOf(Right(Description,X),'0123456789') as Z;
LOAD
"Date",
Money(-1 * Amount) As Amount,
Description,
Len(Description)-15 as X,
if(Index(Description, 'CHECKCARD') = 1, 1, 0) as Y
FROM [lib://Downloads/stmt (3).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)
Where Num(Amount) < 0
There might be some small error in this snippet but I think you will understand the logic of preceeding-load.
- Marcus