Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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;

2 Replies
MVP
MVP

Re: Incremental Loads into Monthly QVDs

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

Not applicable

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

Community Browser