Need help - combine left and right in script

[#1]

Hello again,

i got a huge problem that i cant solve on my own.

Situation: There has been some changes(law) that fakture numbers now look different.

Old Nr: 05-12345 (Left(INVOICEID,2))

new Nr: 12345 - 05 - 1 (Right(INVOICEID, 4))

My script looks like this:

WHERE Left(INVOICEID,2) <>'06' and Left(INVOICEID,2) <>'ER' and Left(INVOICEID,2) <>'15' and Left(INVOICEID,2) <>'09' and Left(INVOICEID,2) <>'12' and Left(INVOICEID,2) <>'23' and Left(INVOICEID,2) <>'22' and Left(INVOICEID,2) <>'24' and Left(INVOICEID,2) <>'25';

drop Table ReLinije;

All faktures that beginn with those numbers are not to be considered in my stats and will be dropped from the resident!.

Problem: Old fakture number will not be changed but i need to compare f. ex. 2012 with 2013 and so on.

What i need/hope for help: How can i combine Left and Right formula in one script, so that All faktures before 2013 fall under LEFT formula and all following on RIGHT?

I hope four your help and thank you in advance.

And a Happy new Year 😃

[#2]

Hello,

You can load old factures in a first time, and the new with the key word CONCATENATE after. Both with their own restrictions.

Amand Dupretz

[Anonymous]

You could load you table twice. The first time with all the rows but setting a flag of whether to include, as follows:

If(Match(Left(INVOICEID,2),'06','ER','15','09','12','23','22','24','25'),1) AS Exclude

Then the second load could have awhere clause to exclude those matching this field:

...

WHERE Exclude = 1;

Jonathan

[#4]

I forgot to mention, that the new facture number changes like this: 1 - 05 - 1, 2 - 05 - 1, etc...

Here is the whole one:

ReLinije:

LOAD CJ_AK,

     CJ_DOG,

     CJ_MIN,

     COMMISSSALESGROUP,

     CUSTACCOUNT,

     DIM10,

     DIM5,

     DIM6_JOUR,

     DIM6_TRANS,

     DLVMODE,

     GROSPROFIT,

     INVENTLOCATIONID,

     INVOICEDATE,

     Mesec,

     Godina,

     INVOICEID,

     ITEMID,

     LINEAMOUNT,

     QTY,

     SALESDISCPERCENT,

     SALESID, //br specke

     STOCKVALUE,

     STOCKVALUE2

FROM

Y:\Linije.qvd (qvd);

Linije:

NoConcatenate LOAD

        CJ_AK,

     CJ_DOG,

     CJ_MIN,

     COMMISSSALESGROUP,

     CUSTACCOUNT,

     DIM10,

     DIM5,

     DIM6_JOUR,

     DIM6_TRANS,

     DLVMODE,

     GROSPROFIT,

     INVENTLOCATIONID,

     INVOICEDATE,

     Left(INVOICEDATE,10) as [Datum_fakture], // TEST

     day(INVOICEDATE) as Dan,

     MonthName(INVOICEDATE) as ImeMjeseca,

     Mesec as Mjesec,

     Godina,

     INVOICEID,

     Left(INVOICEID,2) as [Predbroj fakture],

     text(PurgeChar(text(ITEMID), ' ')) as ITEMID,

     ITEMID as ITEMID1,

     LINEAMOUNT,

     QTY,

     SALESDISCPERCENT,

     SALESID, //br specke

     STOCKVALUE,

     STOCKVALUE2,

     LINEAMOUNT/QTY as [Prodajna cijena]

RESIDENT ReLinije

WHERE Left(INVOICEID,2) <>'06' and Left(INVOICEID,2) <>'ER' and Left(INVOICEID,2) <>'15' and Left(INVOICEID,2) <>'09' and Left(INVOICEID,2) <>'12' and Left(INVOICEID,2) <>'23' and Left(INVOICEID,2) <>'22' and Left(INVOICEID,2) <>'24' and Left(INVOICEID,2) <>'25';

drop Table ReLinije;

Can you tell me where to put your suggestions?

[#5]

Can somebody tell me the formula(in script) for:

If INVOICEDATE <01.01.2013 where left(Invoiceid, 2) <> '06',...

If invoicedate >31.12.2012 where right(Invoiceid,5) <> '-06-1'...

Drop table relinije

I need to combine them somehow that Linije.qvd and relevant data from 2012 and2013 remain in the table