Help in MID function

[theboss-123]

Hello 

i have invoice (FAF-U1200-20163411) and Credit not (AVC-U1200-201600007) is there a way in load statstement to display just invoice which start with "FAF" i hear about Mid function anyone please could give an assist

[BrunPierre]

Hi,

Perhaps with the Mid or SubField function.

Like

If(Mid(Invoice,1,3)='FAF',Invoice) as "FAF Invoices"

or

If(SubField(Invoice,'-',1)='FAF',Invoice) as "FAF Invoices"

[theboss-123]

Hi Pierre thanks for ur reply where I could put this syntaxe under the line of the colomun related to the invoice number in the load script or where after select sql can u send me un examples please 

[BrunPierre]

This way you would have only "FAF" invoices in your data model.

"TRANSACTION_HISTORY":
LOAD "Field1",
"Field2",
"Field3",
"invoice number"
Where SubField("invoice number",'-',1)='FAF';
SQL SELECT *
FROM SERV.dbo."TRANSACTION_HISTORY";

[theboss-123]

Hi Pierre I tried ur suggestion but it still load al' invoice number 

[BrunPierre]

Perhaps with a filter like this.

Where "invoice number" Like '*FAF*';

[Or]

Load * From YourTable

Where Wildmatch(Invoice,'FAF*')

[theboss-123]

Please find my exemple FYI the table  PINVOICED is in Relationship with other  tables

[theboss-123]

Hi Again i tried also the syntaxe below but i got an error

NoConcatenate LOAD *
Resident tab1 where WildMatch(Period, 'Cases*');
DROP Table tab1;

when loading data an error displayed "table tab21 not found"

[BrunPierre]

@theboss-123 The Like operator and the WIldmatch function work similarly.

However, both suggestions differ slightly as ff;

Where Wildmatch(Invoice,'FAF*') ;  //This finds any Invoice values that start with "FAF"

Where "invoice number" Like '*FAF*';  //whereas this finds any Invoice values that have "FAF" in any position