3 Replies Latest reply: Feb 10, 2016 7:11 AM by Freddy Johnsen RSS

    Help with simple append load

      Hi,

       

      I have a qvd file that is updated daily that show in-transit inventory.

      Now I would like to save a snapshot of the in-transit inventory every last day of a month so that I can use this to show monthly trends going forward.

       

      Basicly what I have done is to create a field called  CheckMonthEnd with this logic: If(Date(Now())=MonthEnd(Date(Now())),1,0) as CheckMonthEnd. CheckMonthEnd will have a value 1 or 0.

       

      What I want to do now is to save the file with a date appended to the file name if CheckMonthEnd=1.

       

      Attached is the qvd. Hope somebody could point me in the right direction.

       

      Best regards,
      Freddy

        • Re: Help with simple append load
          Peter Rieper

          Think that you only need to advise the content of the field MaxCheckMonthEnd to the variable?

          If so, please change your line 80 to:

           

          let vCheckMonthEnd = PEEK('MaxCheckMonthEnd', 0, 'MaxMonth');

           

          HTH Peter

          • Re: Help with simple append load
            Peter Cammaert

            In your script, you could do a test for the end of the month (if we assume that your script runs 7 days a week), and store the resident table a second time to a different file. Like

             

            :

            STORE InTransitInventory INTO [IntransitInventory.qvd] (qvd); // Regular daily store

             

            IF Floor(Today()) = Floor(MonthEnd(Today())) THEN

              LET vFileTag = date(Today(), 'YYYYMMDD');

              STORE InstransitInventory INTO [IntransitInventory_$(vFileTag).QVD] (qvd); // Special store

            END IF

            :

             

            Best,

             

            Peter

            • Re: Help with simple append load

              I implemented following solution:

               

              let vSaveDate = date(today(), ‘YYYYMMDD’);

              let vPeriod = MonthName(Now());

               

              let vCheckMonthEnd = If(Date(floor(Now()))=Date(Floor(MonthEnd(Now()))),1,0);

               

              STORE InTransit INTO \\InTransit_Daily_$(vSaveDate).qvd;

               

               

              if  $(vCheckMonthEnd) = 1 then

               

              STORE InTransit INTO \\InTransit_Monthly_$(vPeriod).qvd;

               

              end if