topic Re: Incremental load - INNER JOIN not working? in QlikView

Incremental load - INNER JOIN not working?

sarahallen1 — Fri, 17 Aug 2012 09:27:22 GMT

I am trying to tweak the reference guide's incremental load example (case 4: insert, update and delete - as on p512 of the attached reference manual) as follows:

Instead of using a database table with records, I want to use a folder location with Excel files. I want the incremental load to work so that only new/updated files are read in, and any that have been deleted since the last execution are not loaded in. For the moment I'm just trying to get a single QV table with 2 fields: FileName() as File, and FileTime() as ModifiedDate. So my equivalent to the PrimaryKey field is FileName(), my equivalent to DB_TABLE is sort of the folder of files, and instead of a DB field "ModificationTime" I'm using FileTime().

Later on when I've got this simple file list load to work I'm going to loop over each file in the list and read in data from each (I've done this before successfully but without the incremental load). But for now I can't even get the incremental load to work with the file list.

My script is attached. I have started the process off by just commenting out lines 23-24, 32-36 and 47-51. So this just gets the complete file list when I reload (and creates an initial QVD with the full list). Then I have uncommented 23-24 and 32-36, and this works - it gets any new files (18-26) and any "old" files that haven't been updated/added since the last execution. This works, and after such a reload I get the QVD which I've attached.

My problem comes when I uncomment lines 47-52, the inner join statement - which I believe is supposed to cut out any files that have deleted since the last script execution (as the inner join returns the intersection of the current full file list with the new/updated/old ones, thus removing any deleted ones which are in the new/updated/old set). When I load the script with this bit uncommented, there are no errors but all my data goes - as if the intersection is empty. But this can't be right, as I've tried the script with everything commented out EXCEPT 48-52 (the table we are inner joining) and this is the same set of data as I had with ONLY 47-52 commented out.

I hope this makes sense.

I've attached the source files I've been using too so you can test it yourself.

Thanks very much for your help - any explanation would be really appreciated!

Regards,

Sarah

Re: Incremental load - INNER JOIN not working?

rwunderlich — Tue, 21 Aug 2012 02:11:58 GMT

I think you've got a number of problems with this approach.

1. The INNER JOIN deletes all rows because with the wildcard file, the inner join is executed for each file -- with one row each. Turn on the document logfile in Settings, Document properties to see this.

2. I understand you want to build a list of current files in the directory and only do a reload for new files. Your wildcard approach will do this, but you may find it easier to use FOR EACH file in FILELIST().

3. I would just build a table of files in the directory and then filter by date in the load loop. I wouldn't try to filter ion the table building loop.

Let me know if this helps

-Rob

Re: Incremental load - INNER JOIN not working?

sarahallen1 — Tue, 21 Aug 2012 08:36:53 GMT

Thanks very much Rob, this is really helpful.

I understand point 1. now, it makes sense - should have thought to check the logfile so thanks for the tip! For the moment, I've fixed it by creating a temporary table island "CurrentFileList" at the beginning of the script (with the * wildcard to pick up all files), then referring to this single table (rather than having to use a wildcard) when I come to the INNER JOIN statement later on.

The FOR EACH file in FILELIST() approach sounds sensible, thanks for the suggestion. I will try it out and update this discussion when I know how it worked! I have a lot of scripting to do now I've got the basic version of the incremental load working, so when I need to do an incremental load over a much more complicated script I think the FOR EACH loop idea will help a lot.

Your help is really appreciated

Sarah

Re: Incremental load - INNER JOIN not working?

Wed, 30 Jan 2013 08:10:49 GMT

hi sarah, can you please share some example code for this.

Thanks in advance

Re: Incremental load - INNER JOIN not working?

sarahallen1 — Wed, 30 Jan 2013 09:30:31 GMT

Sure, but be warned - it's a long and complicated script! It is working well, but there are lots of components to the script which are not directly relevant to the incremental load problem. Hopefully the green comments will help (I've used them to divide the script up into sections).

Here comes...

Re: Incremental load - INNER JOIN not working?

sarahallen1 — Wed, 30 Jan 2013 09:30:56 GMT

///$tab START

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

Let vThisExecTime = date#(Now());

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

///$tab Main

SET ThousandSep=','; SET DecimalSep='.'; SET MoneyThousandSep=','; SET MoneyDecimalSep='.'; SET MoneyFormat='£#,##0.00;(£#,##0.00)'; SET TimeFormat='hh:mm:ss'; SET DateFormat='DD/MM/YYYY'; SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

///$tab Daily Prices - inc load FIRST STEP //SET vFileLocation = 'Daily prices - all funds - SA TEST\Daily price files\';

SET vFileLocation = '\\SERVER\folder1\folder2\';

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // CURRENT FILE LIST //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

//========================================================================================================== // Make a list of the current files //==========================================================================================================

CurrentFileList:

//OEICS:

FIRST 1 LOAD DISTINCT FileName() as File_tempFROM [$(vFileLocation)*MAM??-??-??*.csv] (txt, codepage is 1252, no labels, delimiter is ',', ms Concatenate

//Onshore:

FIRST 1 LOAD DISTINCT FileName() as File_tempFROM [$(vFileLocation)*MAMLSFund??-??-??*.csv] (txt, codepage is 1252, no labels, delimiter is Concatenate

//LSFund:

FIRST 1 LOAD DISTINCT FileName() as File_tempFROM [$(vFileLocation)*Price Sheet*.xls] (biff, embedded labels, header is 3 lines, table is Sheet1$ Concatenate

//Global:

FIRST 1 LOAD DISTINCT FileName() as File_tempFROM [$(vFileLocation)*Prices Sheet*.xls] (biff, embedded labels, header is 3 lines, table is Sheet1

//========================================================================================================== // Store the list of current files as a QVD (to refer to later in the inner join)//==========================================================================================================

STORE File_temp as File FROM CurrentFileList INTO CurrentFileList.QVD;

//========================================================================================================== // Make a variable listing the current files (to use in the "For each vFile in vFileList" statement later)//==========================================================================================================

LET vFileList=''; //Start with a blank list

For i=0 to NoOfRows('CurrentFileList')-1

let vFileList = vFileList & Peek('File_temp',i,'CurrentFileList') & ',' ;

//Concatenate list of current files (with ',' separator)

Next;

LET vFileList = chr(39)& replace(Left('$(vFileList)', len('$(vFileList)')-1) , ',' //Remove the final , chr(39)&','&chr(39) ) &chr(39) ; //The replace statement and surroundi

// '2012-07-06 - MAM06-07-12.csv','2012-07-09 - MAM09-07-12.csv',.............,'2012-07-10 - MAMedi // This is the resulting format of vFileList

//========================================================================================================== // Drop the script table once file list created (in QVD and vFileList), so as not to interfere with the fie //==========================================================================================================

DROP Table CurrentFileList;

///$tab Daily Prices - data no dates//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // NEW/UPDATED FILES//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Data: //Start the table off so we can concatenate to it later on

LOAD * INLINE [

File, Price, FundClass

Dummy, Dummy, Dummy];

// WORK THROUGH THE CURRENT FILE LIST AND PICK OUT DATA FOR THOSE FILES WHICH ARE NEW/UPDATED SINCE LAST EX

// For each vFile in '2012-07-06 - MAM06-07-12.csv','2012-07-09 - MAM09-07-12.csv','2012-07-10 - MAM

For each vFile in $(vFileList)

//========================================================================================================== IF FileTime('$(vFileLocation)'&'$(vFile)') < '$(vLastExecTime)' THEN // If the file has NOT been adde //==========================================================================================================

LET vDummy = '$(vFile)'; // vDummy statement achieves nothing, just a quick script event to make

//----------------------------------------------------------------------------------------------

//OEICs

//----------------------------------------------------------------------------------------------

IF '$(vFile)' like '*MAM??-??-??*.csv' THEN

//----------

Concatenate //Data:

//----------

LOAD FileName() as File,

[Unrounded Prices] as Price,

[Fund/Class] as FundClass,

FileName()&'_'&[Fund/Class] as PrimaryKey_File_FundClass

,Yield //ADDED IN 29/10/2012

FROM [$(vFileLocation)$(vFile)]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines); //----------------------------------------------------------------------------------------------//Onshore Global & LSFund//----------------------------------------------------------------------------------------------

ELSEIF '$(vFile)' like '*MAMLSFund??-??-??*.csv' THEN

//----------Concatenate //Data://----------

LOAD FileName() as File, [Unrounded Prices] as Price, [Fund/Class] as FundClass, FileName()&'_'&[Fund/Class] as PrimaryKey_File_FundClass

,Yield //ADDED IN 29/10/2012FROM [$(vFileLocation)$(vFile)](txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

//----------------------------------------------------------------------------------------------//Offshore LSFund//----------------------------------------------------------------------------------------------

ELSEIF '$(vFile)' like '*Price Sheet*.xls' THEN

//----------Concatenate //Data://----------

LOAD FileName() as File, Name as FundClass, [NAV / Share] as Price, FileName()&'_'&Name as PrimaryKey_File_FundClass

FROM

[$(vFileLocation)$(vFile)](biff, embedded labels, header is $(if(only(@2)='Previous NAV',2,3)) lines, //4-Jan-13 edit: the format sometime table is Sheet1$, filters( Remove(Row, RowCnd(CellValue, 4, StrCnd(null))) ));

//----------------------------------------------------------------------------------------------//Offshore Global//----------------------------------------------------------------------------------------------

ELSEIF '$(vFile)' like '*Prices Sheet*.xls' THEN

//----------Concatenate //Data://----------

LOAD FileName() as File, Name as FundClass, [NAV / Share] as Price, FileName()&'_'&Name as PrimaryKey_File_FundClass

FROM [$(vFileLocation)$(vFile)](biff, embedded labels, header is 2 lines, table is Sheet1$, filters( Remove(Row, RowCnd(CellValue, 4, StrCnd(null))) ));

//----------------------------------------------------------------------------------------------//NB: Shouldn't actually reach this 'ELSE'. The various formats of vFile covered in the IF/ELSEIF s // should be the only posibilities for vFile, given the construction of CurrentFileList and //----------------------------------------------------------------------------------------------

ELSE LET vDummy = '$(vFile)'; // vDummy statement achieves nothing, just a quick script event to make

//----------------------------------------------------------------------------------------------

ENDIF;

//----------------------------------------------------------------------------------------------

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

ENDIF;

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

Next vFile;

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // FILES THAT ARE NOT NEW/UPDATED (could include files that have since been deleted)//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Concatenate LOAD DISTINCT * //File, Price, FundClass, PrimaryKey_File_FundClass From [PricesHistory_Data.QVD] (qvd)WHERE Not Exists(PrimaryKey_File_FundClass) //Where we didn't pick up the data in the stage above (i.e. igno ;

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // EXPORT TO QVD//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

STORE * //File, Price, FundClass, PrimaryKey_File_FundClass FROM Data INTO PricesHistory_Data.QVD ;

DROP Field PrimaryKey_File_FundClass;

///$tab Daily Prices - separate dates//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // NEW/UPDATED FILES//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Dates: //Start the table off so we can concatenate to it later on

LOAD * INLINE [

File2, Date

Dummy, Dummy];

// WORK THROUGH THE CURRENT FILE LIST AND PICK OUT DATA FOR THOSE FILES WHICH ARE NEW/UPDATED SINCE LAST EX

// For each vFile in '2012-07-06 - MAM06-07-12.csv','2012-07-09 - MAM09-07-12.csv','2012-07-10 - MAM

For each vFile in $(vFileList)

LET vDummy = '$(vFile)'; // vDummy statement achieves nothing, just a quick script event to make

//========================================================================================================== ELSE // If the file HAS been added/updated since last execution://========================================================================================================== //----------------------------------------------------------------------------------------------//OEICs//----------------------------------------------------------------------------------------------

IF '$(vFile)' like '*MAM??-??-??*.csv' THEN

//----------Concatenate //Dates://----------

FIRST 1 LOAD FileName() as File2,

@4 as Date //date(date#(@4),'DD/MM/YYYY') as DateFROM [$(vFileLocation)$(vFile)](txt, codepage is 1252, no labels, delimiter is ',', msq);

//----------------------------------------------------------------------------------------------//Onshore Global & LSFund//----------------------------------------------------------------------------------------------

ELSEIF '$(vFile)' like '*MAMLSFund??-??-??*.csv' THEN

//----------Concatenate //Dates://----------

FIRST 1 LOAD FileName() as File2,

@4 as Date //date(date#(@4),'DD/MM/YYYY') as DateFROM [$(vFileLocation)$(vFile)](txt, codepage is 1252, no labels, delimiter is ',', msq);

ELSEIF '$(vFile)' like '*Price Sheet*.xls' THEN

//----------Concatenate //Dates://----------

LOAD FileName() as File2,

date(date#([Pricing Date]),'DD/MM/YYYY') as Date FROM [$(vFileLocation)$(vFile)](biff, embedded labels, header is $(if(only(@2)='Previous NAV',2,3)) lines, //4-Jan-13 edit: the format sometime table is Sheet1$, filters( Remove(Row, RowCnd(CellValue, 4, StrCnd(null))) ));

ELSEIF '$(vFile)' like '*Prices Sheet*.xls' THEN

//----------Concatenate //Dates://----------

LOAD FileName() as File2,

date(date#([Pricing Date]),'DD/MM/YYYY') as Date FROM [$(vFileLocation)$(vFile)](biff, embedded labels, header is 2 lines, table is Sheet1$, filters( Remove(Row, RowCnd(CellValue, 4, StrCnd(null))) ));

ELSE LET vDummy = '$(vFile)'; // vDummy statement achieves nothing, just a quick script event to make

//----------------------------------------------------------------------------------------------

ENDIF;

//----------------------------------------------------------------------------------------------//==========================================================================================================

ENDIF;

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

Next vFile;

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // FILES THAT ARE NOT NEW/UPDATED (could include files that have since been deleted)//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Concatenate LOAD DISTINCT * //File2, DateFrom [PricesHistory_Dates.QVD] (qvd)WHERE Not Exists(File2) //Where we didn't pick up the data in the stage above (i.e. ignore updated files as;

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // EXPORT TO QVD//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

STORE * //File2, DateFROM Dates INTO PricesHistory_Dates.QVD ;

///$tab Daily Prices - join data and dates

Daily_Prices:

NoConcatenate

LOAD DISTINCT

File,Price,FundClass

,Yield //ADDED IN 29/10/2012Resident Data;

Left Join (Daily_Prices)

LOAD DISTINCT File2 as File,Date

Resident Dates;

DROP Tables Data, Dates;

///$tab Daily Prices - inc load LAST STEP//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // CUT OUT ANY FILES THAT HAVE BEEN DELETED SINCE LAST EXECUTION //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Inner Join //To Daily_Prices (=Dates+Data) tablea aboveLOAD DISTINCT * //FileFROM CurrentFileList.QVD (qvd);

//NB This inner join also cuts out the 'Dummy' record ('Dummy' for File, Price, FundClass, Date),// since it gives the intersection with the current list of files (which includes no dummies)

///$tab History//************************************************************************************//BRINGING IN THE FULL HISTORY NOW (SEPARATE SOURCE - ALL FUNDS TOGETHER)// This history table will now be concatenated with the "Daily_Prices" table above

Concatenate

//Prices_History:

LOAD date(Date,'DD/MM/YYYY') as Date, FundClass,Price //,//date(Date,'DD/MM/YYYY')&'__'&FundClass&'__'&Price as #Date__FundClass__Price

FROM

[\\MAM-file01\MAM\Operations\Daily prices - all funds - SA TEST\SOURCE - Historical Prices.xlsx]

// [Daily prices - all funds - SA TEST\SOURCE - Historical Prices.xlsx]

(ooxml, embedded labels, table is [AS IMPORTED 7 AUG 2012]);

//************************************************************************************

///$tab Fund identifiers etc

NoConcatenate

Fund_identifiers:

LOAD FundClass,Fund,[Share Class],Sedol,ISIN,Currency

FROM

[\\MAM-file01\MAM\Operations\Daily prices - all funds - SA TEST\SOURCE - Fund identifiers.xls]

//[Daily prices - all funds - SA TEST\SOURCE - Fund identifiers.xls]

(biff, embedded labels, table is Sheet1$);

//------------------------------------------// Now join the identifier info into the data table//-------------------------------------------

NoConcatenate

Prices_temp:Load * Resident Daily_Prices; Left Join (Prices_temp) Load * Resident Fund_identifiers; DROP Tables Daily_Prices, Fund_identifiers;

//------------------------------------------// Now tidy up the prices/currencies so everything is in MAMor units, e.g. 1.0175 GBP rather than 101.75 GB //-------------------------------------------

NoConcatenate

Prices_final:

LOAD //#Date__FundClass__Price,

Date,FundClass,if(Currency='GB Pence' OR Currency='USD Cent' OR Currency='EUR Cent',Price/100,Price) as Price, Fund,[Share Class],Sedol,ISIN,

if(Currency='GB Pence','GBP', if(Currency='USD Cent', 'USD', if(Currency='EUR Cent', 'EUR', Currency))) ,Yield //ADDED IN 29/10/2012Resident Prices_temp;

DROP Table Prices_temp;

///$tab Export Prices to CSV

//***************************************************************************

// Temporarily disabled whilst editing script

//***************************************************************************

////STORE// Date, // Price,// Fund,// [Share Class], // Sedol, // ISIN,// Currency//FROM Prices_final //INTO \\MAM-file01\MAM\Operations\Daily prices - all funds - SA TEST\EXPORT_Prices.csv (txt);

STORE Date,Price,Fund,[Share Class],Sedol,ISIN,Currency

FROM Prices_final

INTO \\MAM-file01\MAM\Operations\Daily prices -all funds -SA TEST\EXPORT_Prices.csv (txt);

////STORE// Date, // Price,// Fund,// [Share Class], // Sedol, // ISIN,// Currency//FROM Prices_final //INTO EXPORT_Prices2.csv (txt);///$tab Dividends

//----------------------------------------------------------------------

NoConcatenate

Dividends_temp1:

LOAD date([XD Date],'DD/MM/YYYY') as [XD Date],

//*****

date([XD Date],'DD/MM/YYYY') as Date,

//*****

date([Pay Date],'DD/MM/YYYY') as [Pay Date],

Sedol,

//[Fund Name],//[Share Class Name],//[Share Class Number],//[Interest / Dividend],

// Currency,

[Distribution Rate - Gross],[Distribution Rate - Net]

FROM

[\\MAM-file01\MAM\Operations\Daily prices - all funds - SA TEST\SOURCE - historical dividend rates.xlsx]

//[Daily prices - all funds - SA TEST\SOURCE - historical dividend rates.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Dividends, filters(

Remove(Row, RowCnd(Compound,RowCnd(CellValue, 8, StrCnd(null)), RowCnd(CellValue, 9, StrCnd(null))

))));

//----------------------------------------------------------------------

NoConcatenate

Fund_identifiers_2:

LOAD FundClass,Fund,[Share Class],Sedol,ISIN,Currency

FROM

[\\MAM-file01\MAM\Operations\Daily prices - all funds - SA TEST\SOURCE - Fund identifiers.xls]

//[Daily prices - all funds - SA TEST\SOURCE - Fund identifiers.xls]

(biff, embedded labels, table is Sheet1$);

//------------------------------------------// Now join the identifier info into the data table//-------------------------------------------

NoConcatenate

Dividends_temp2: Load * Resident Dividends_temp1; Left Join (Dividends_temp2) Load * Resident Fund_identifiers_2; DROP Tables Dividends_temp1, Fund_identifiers_2;

//------------------------------------------// Now tidy up the prices/currencies so everything is in MAMor units, e.g. 1.0175 GBP rather than 101.75 GB //-------------------------------------------

NoConcatenate

Dividends:

LOAD Date,[XD Date],[Pay Date],FundClass,if(Currency='GB Pence' OR Currency='USD Cent' OR Currency='EUR Cent',[Distribution Rate - Gross]/100,[Di if(Currency='GB Pence' OR Currency='USD Cent' OR Currency='EUR Cent',[Distribution Rate - Net]/100,[Dist Fund,[Share Class],Sedol,ISIN,if(Currency='GB Pence','GBP', if(Currency='USD Cent', 'USD', if(Currency='EUR Cent', 'EUR', Currency)))

Resident Dividends_temp2;

DROP Table Dividends_temp2;

///$tab Export Dividends to CSV//***************************************************************************// Temporarily disabled whilst editing script//***************************************************************************

////STORE// [XD Date], // [Pay Date],// [Distribution Rate - Gross],// [Distribution Rate - Net],// Fund,// [Share Class], // Sedol, // ISIN,// Currency

//FROM Dividends//INTO \\MAM-file01\MAM\Operations\Daily prices - all funds - SA TEST\EXPORT_Dividends.csv (txt);

STORE

[XD Date],[Pay Date],[Distribution Rate - Gross],[Distribution Rate - Net],Fund,[Share Class],Sedol,ISIN,Currency

FROM Dividends INTO \\MAM-file01\MAM\Operations\Daily prices -all funds -SA TEST\EXPORT_Dividends.csv (txt);

//STORE

// [XD Date],

// [Pay Date],

// [Distribution Rate - Gross],

// [Distribution Rate - Net],

// Fund,

// [Share Class],

// Sedol,

// ISIN,

// Currency

//FROM Dividends

//INTO EXPORT_Dividends2.csv (txt);

///$tab Join Prices & Dividends

NoConcatenate

Prices_and_Dividends_temp: Load * Resident Prices_final; Join (Prices_and_Dividends_temp) Load * Resident Dividends; DROP Tables Prices_final, Dividends;

Prices_and_Dividends:

LOAD *,day(Date) as Day,month(Date) as Month, year(Date) as Year

Resident Prices_and_Dividends_temp;

DROP Table Prices_and_Dividends_temp;

///$tab FINISH

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

If ScriptErrorCount = 0 then

Let vLastExecTime = vThisExecTime;

End If

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