4 Replies Latest reply: Jan 3, 2013 4:53 AM by Kristian König RSS

    Need help - combine left and right in script

    Kristian König

      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 =)

        • Re: Need help - combine left and right in script

          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

          • Re: Need help - combine left and right in script

            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

            • Re: Need help - combine left and right in script
              Kristian König

              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?

              • Re: Need help - combine left and right in script
                Kristian König

                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