topic Re: Help in MID function in Reporting Service & Alerting

Help in MID function

theboss-123 — Fri, 18 Nov 2022 22:44:58 GMT

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

Re: Help in MID function

BrunPierre — Sat, 19 Nov 2022 17:46:26 GMT

Hi,

Perhaps with the Mid or SubField function.

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

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

Re: Help in MID function

theboss-123 — Sat, 19 Nov 2022 19:07:34 GMT

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

Re: Help in MID function

BrunPierre — Sat, 19 Nov 2022 20:58:24 GMT

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";

Re: Help in MID function

theboss-123 — Sun, 20 Nov 2022 09:56:59 GMT

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

Re: Help in MID function

BrunPierre — Mon, 21 Nov 2022 07:14:48 GMT

Perhaps with a filter like this.

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

Re: Help in MID function

Or — Sun, 20 Nov 2022 11:22:53 GMT

Load * From YourTable

Where Wildmatch(Invoice,'FAF*')

Re: Help in MID function

theboss-123 — Sun, 20 Nov 2022 12:41:14 GMT

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

Re: Help in MID function

theboss-123 — Mon, 21 Nov 2022 01:08:14 GMT

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"

Re: Help in MID function

BrunPierre — Mon, 21 Nov 2022 07:35:30 GMT

@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

Re: Help in MID function

BrunPierre — Mon, 21 Nov 2022 07:41:12 GMT

Provide the whole load script.

Re: Help in MID function

theboss-123 — Mon, 21 Nov 2022 12:09:04 GMT

the informations are availble on FACT_Facture the filter could be apply on "NUM_0" as Dim6

Re: Help in MID function

BrunPierre — Mon, 21 Nov 2022 13:21:17 GMT

See attached. The filter is applied at the backend and as a calculated expression. Choose what works best for you.

Re: Help in MID function

theboss-123 — Mon, 21 Nov 2022 14:34:48 GMT

Thanks a lot Pierre for your time and your help this is what i was looking for thanks again