Incremental Loop Daily CSVs into Monthly QVDs then Master QVD

Tue, 11 Aug 2015 16:02:21 GMT

All,

I am NEW to this program coding and I'm having great frustration with this incremental loop. I've read many great blogs and how-to's but still can't figure this out or find something more closely to what I want to accomplish. And, everything I've tried to-date isn't working. Is there anyone out there that can help?

Thanks,

Mindy

Scenario:

Daily csv files are loaded into folders.

1) I want to load only new csv files into qvd files (this part is done).

ASRCompliant31-Jul-2015_00_00.csv > ASRCompliant31-Jul-2015_00_00.qvd

ASRCompliant11-Aug-2015_00_00.csv > ASRCompliant11-Aug-2015_00_00.qvd

2) Load only new rows of data into a corresponding Monthly QVD based on the date field, SESSION_START_TIME (aka EventMth).

ASRCompliant31-Jul-2015_00_00.qvd > Compliant_Events_Jul-2015.qvd

ASRCompliant01-Aug-2015_00_00.qvd > Compliant_Events_Aug-2015.qvd...and possibly...Compliant_Events_Jul-2015.qvd

3) Load the Monthly QVDs into a Master QVD.

Compliant_Events_Jul-2015.qvd > Combined_Events.qvd

Compliant_Events_Aug-2015.qvd > Combined_Events.qvd

STEP 1:

// ===== Auto load source Compliant .csv files into .qvd files with same name in different folder =====//

SET vSourcePath = 0;
LET vSourcePath = 'R:\Events\TEST\Compliant\';

FOR Each vCSVFile in FileList('$(vSourcePath)*.csv')

LET vBaseName = Mid(SubField('$(vCSVFile)','\',-1), 1, Index(SubField('$(vCSVFile)','\',-1),'.',-1)-1);
IF IsNull(FileTime('$(vSourcePath)\QVDs\$(vBaseName).qvd')) then

tmpTable:
LOAD

'Compliant' as Compliancy,
DATA_SRC_ID as [Event ID],

                        DATA_SOURCE,
                        MonthName(Date#(SESSION_START_TIME, 'DD-MMM-YYYY hh:mm:ss')) as EventMth,
                        FileName() as Source_File,
                        FileBaseName() as File_Base_Name,
                        FilePath() as File_Path

From

                       [$(vSourcePath)\$(vBaseName).csv]
                       (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
                STORE tmpTable into [$(vSourcePath)\QVDs\$(vBaseName).qvd] (qvd);
                DROP Table tmpTable;
      END If
NEXT vCSVFile

STEP 2: ?

STEP 3: ?

topic Incremental Loop Daily CSVs into Monthly QVDs then Master QVD in QlikView

Incremental Loop Daily CSVs into Monthly QVDs then Master QVD