2 Replies Latest reply: Aug 19, 2015 4:36 PM by Mindy Kay RSS

    Incremental Loads into Monthly QVDs

      All,

       

      The below code works in loading new monthly QVDs but doesn't when new data needs to be added to already created monthly QVDs.  What am I doing wrong in the "ELSE" part of the statement?  Everything I've tried so far gets me the same results.

       

      Scenario:

      On a daily basis, I'm converting new .csv files into new .qvd files.  All of the data from the .qvd files are then added to the Combined_Events.qvd file.  From there, I want to create and update monthly .qvd files based on the EventMthYr.  I have all of this working except for the updating of the monthly .qvd files.

       

      Step 1: Convert daily .csv files to .qvd files                            // code working

      Step 2: Create "Combined Events.qvd"                                 // code working

      Step 3: Create "Combined Events - MMM - YYYY.qvd"          // code working

      Step 4: Update "Combined Events.qvd"                                //  code working

      Step 5: Update "Combined Events - MMM - YYYY.qvd"         //  not working ... need help !!!!

       

      Thanks,

      Mindy

       

      // =============================================================================================== //

      // =============================   Load Combined Events into monthly QVDs   ============================= //
      // =============================================================================================== //

       

      TRACE;
      TRACE ==== Monthly Combined Interactive Events ====;

      SET vSourcePath = 0;
      LET vSourcePath = 'E:\share\TAM\QlikView\Common Reference\Data\TESTING';

      EventMthYr:
      LOAD Distinct
           EventMth as MonthYr
      From
           [$(vSourcePath)\Combined_Events.qvd] (
      qvd);

      //Order By EventMth asc; ... error message

      SET vCount = 0;

      LET vCount = NoOfRows('EventMthYr');

      FOR i = 0 to $(vCount)-1


           SET vMonthYr = 0;

           LET vMonthYr = Peek('MonthYr',$(i),'EventMthYr');

           IF(IsNull(FileTime('$(vSourcePath)\Combined Events - $(vMonthYr).qvd'))) then

                [$(vMonthYr)]:
                LOAD
                     *,
                     '$(vMonthYr)'
      as Month1
                From

                     [$(vSourcePath)\Combined_Events.qvd] (qvd)
                Where
                     EventMth = '$(vMonthYr)';

                STORE [$(vMonthYr)] into [$(vSourcePath)\Combined Events - $(vMonthYr).qvd] (qvd);
                DROP Table [$(vMonthYr)];

       

           ELSE


                [$(vMonthYr)]:
                LOAD

                     *
                From

                     [$(vSourcePath)\Combined Events - $(vMonthYr).qvd] (qvd)
                Where

                     EventMth = '$(vMonthYr)';

                Concatenate

                [$(vMonthYr)]:
                LOAD
                     *,
                     '$(vMonthYr)'
      as Month1
                From
                     [$(vSourcePath)\Combined_Events.qvd] (
      qvd)
                Where
                     EventMth = '$(vMonthYr)'
                     and not Exists ([Event ID]);

                STORE [$(vMonthYr)] into [$(vSourcePath)\Combined Events - $(vMonthYr).qvd] (qvd);
                DROP Table [$(vMonthYr)];

       

           ENDIF

       

      NEXT

      SET i=;

      TRACE ==== End Monthly Combined Interactive Events ====;

      TRACE;
      TRACE == End Script == ;

      EXIT Script;

        • Re: Incremental Loads into Monthly QVDs
          Stefan Wühl

          'not working' is not a good issue description, could you elaborate a bit more detailed?

          Do you get an error message or what is the deviation between result and expectation?

           

          I haven't really understood your if.. then logic, are you running the script twice so both branches will be executed for each MonthYear?

           

          Then, I would run the script in the debugger and carefully watch all variables expansions. Also check all WHERE clauses (especially WHERE NOT EXISTS should be double checked).

            • Re: Incremental Loads into Monthly QVDs

              On a daily basis, add new data to “Combined Events.qvd”.  For each month year, pull those data rows and put into their own month year qvd (ex. “Combined Events – Jul 2015.qvd”).  If data rows in “Combined Events.qvd” exist with EventMth = Aug 2015 but no “Combined Events – Aug 2015.qvd” exist, then create and populate the new qvd.  If “Combined Events – Aug 2015.qvd” already exists, then add/concatenate the new data rows to the qvd.

               

              Compliant30-Jul-2015.csv   > Compliant30-Jul-2015.qvd  > Combined Events.qvd > Combined Events - Jul 2015.qvd

              Compliant31-Jul-2015.csv   > Compliant31-Jul-2015.qvd  > Combined Events.qvd > Combined Events - Jul 2015.qvd

              Compliant01-Aug-2015.csv > Compliant01-Aug-2015.qvd > Combined Events.qvd > Combined Events - Aug 2015.qvd

              Compliant02-Aug-2015.csv > Compliant02-Aug-2015.qvd > Combined Events.qvd > Combined Events - Aug 2015.qvd

               

              The updating of already created monthly qvds is what I can't get the code to do.  No error message.  The code runs but no NEW rows of data are loaded into the monthly qvds.

               

              What I want the code to do:

              - Compliant01-Aug-2015.qvd is created                           ( row count = 1,500 )

                     -> Combined Events.qvd updated                            ( row count = 1,000,000 )

                     -> Combined Events - Aug 2015.qvd is created        ( row count = 1,500 )

               

              - Compliant02-Aug-2015.qvd is created                          ( row count = 2,000 )

                      -> Combined Events.qvd updated                          ( row count = 1,002,000 )

                      -> Combined Events - Aug 2015.qvd updated         ( row count = 3,500 )  ... this update is not happening