Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am reading lines from a text file and want to extract some specific data from some rows into different fields. It is working in the DataRaw table.
To not use Mid(@1, 22) all the time within the string operations esp. if it gets more complicated for the string parts I have to reload the needed field from the DataRaw table into a new table AFAIK.
But in the DataAnalysis more data is extracted from the line. It looks for me like the Wildmatch is not working properly there.
Can anyone help me with this issue? Where is my mistake?
Thanks
Christian
DataRaw:
LOAD DISTINCT
filename() as source,
Mid(@1, 22) as line,
if(Wildmatch(Mid(@1, 22), '*Condition AA*', '*Condition AB*')=1, Replace(Mid(Mid(@1, 22), 30), 'TextA', ''), Replace(Mid(Mid(@1, 22), 56), 'TextB', '')) as Field1,
if(Wildmatch(Mid(@1, 22), '*Condition BA*')=1, Mid(Mid(@1, 22), 39, 17)) as Field2
FROM
[data.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq)
where wildmatch(@1, '*Textcondition*');
DataAnalysis:
LOAD DISTINCT
@1 as raw_line,
if(Wildmatch(@1, '*Condition AA*', '*Condition AB*')=1, Replace(Mid(@1, 30), 'TextA', ''), Replace(Mid(@1, 56), 'TextB', '')) as Field1a,
if(Wildmatch(@1, '*Condition BA*')=1, Mid(@1, 39, 17)) as Field2
FROM_FIELD
(DataRaw, line)
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
If I understand it correctly in DataAnalysis the wildmatch function is operating on a longer string than in DataRaw. So it may encounter more matches in the part of the string that is not processed in DataRaw because of the mid(@1, 22).
No, the string should be the same.
I extract in DataRaw Mid(@1, 22) as line.
In DataAnalysis I load line from DataRaw again.
Was about to write same as Gysbert,
what if modifying the condition into
if(Wildmatch(Mid(@1, 22), '*Condition BA*'), ...
HTH
Peter
That is what I am doing in DataRaw and it works. But I would like to shorten the condition to a more readable form since more difficult string operations will follow.
Thats why I reload the line from DataRaw (which is Mid(@1, 20) from the text file into @1 from DataAnalysis.
If I am not absolutely wrong the DataAnalysis.@1 is DataRaw.line. But the code is not working anymore.
I found one issue:
I have two RawData tables with the same field. If I reload the field from RawData1 also lines from RawData2 are included in that new table.
I expected that the tables (even with same field names) are separated.
You have one DataRaw table and two loads the load data in that table. The result of both loads are concatenated because they have exactly the same fields so the result is one table with records from both loads. In one 'DataRaw' load you do a wildmatch and that processes only the records you then load into the DataRaw. But in DataAnalysis you process the data that was loaded into the DataRaw table with both loads. So the wildmatch in DataAnalysis processes more rows.
Yes, but I have 3 Loads.
RawData1:
Load with stuff as fieldA
RawData2:
Load with other stuff as fieldA
DataAnalysis:
Load From_Field(RawData1, fieldA) with the WildMatch stuff as fieldB
I expected that I only get fieldA data from RawData1 and not from RawData2, too.
That was a missunderstanding.