topic Incremental Loads into Monthly QVDs in QlikView

Incremental Loads into Monthly QVDs

Wed, 19 Aug 2015 19:47:24 GMT

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

swuehl — Wed, 19 Aug 2015 19:57:58 GMT

'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

Wed, 19 Aug 2015 20:36:54 GMT

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