Incremental Loads into Monthly QVDs

[#1]

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;

[swuehl]

'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).

[#3]

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