I have a number of XML documents where I am trying to do a simple sum of records based on a certain date. Below is the code, and I have added in a function to extract the date. The records I need to see is the RecordCount in the MessageSent table.. There seems to be no link between the two when I do this. Could anyone point out what I am doing wrong?
Also attached a reduced example
Thanks Paul
// Start of [DSO_MIM602_1358321939079.xml] LOAD statements
MessagesSent:
LOAD MessageTypeCode,
RecordCount,
%Key_ieXMLDocument_C7BCA21E04157E28 // Key to parent table: ieXMLDocument
FROM C:\Users\Paul\Desktop\TIBCO\RAW\MPCC_Received\DSO_MIM602*.xml (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesSent]);
MessagesReceived:
LOAD MessageTypeCode,
RecordCount,
%Key_ieXMLDocument_C7BCA21E04157E28 // Key to parent table: ieXMLDocument
FROM C:\Users\Paul\Desktop\TIBCO\RAW\MPCC_Received\DSO_MIM602*.xml (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesReceived]);
ieXMLDocument:
LOAD
date(date#(left([MIM602_DailySummaryReconciliationCount/StartTime],10),'YYYY-MM-DD')-1,'DD/MM/YYYY') as MM_DATE,
[MIM602_DailySummaryReconciliationCount/StartTime] as StartTime,
[MIM602_DailySummaryReconciliationCount/EndTime] as EndTime,
[MessageHeader/MessageTypeCode],
[MessageHeader/VersionNumber] as VersionNumber,
[MessageHeader/MarketTimestamp] as MarketTimestamp,
[MessageHeader/SenderID] as SenderID,
[MessageHeader/RecipientID] as RecipientID,
[MessageHeader/TxRefNbr] as TxRefNbr,
%Key_ieXMLDocument_C7BCA21E04157E28 // Key for this table: ieXMLDocument
FROM C:\Users\Paul\Desktop\TIBCO\RAW\MPCC_Received\DSO_MIM602*.xml (XmlSimple, Table is [ieXMLDocument]);
// End of [DSO_MIM602_1358321939079.xml] LOAD statements
You need to loop on the xml-files. Something like this:
*********
FOR each FILE in filelist ('.\RAW\MPCC_Received\*.xml')
// Start of [DSO_MIM602_1358321939079.xml] LOAD statements MessagesSent: LOAD MessageTypeCode, RecordCount, FileName(FILE) as Key // %Key_ieXMLDocument_C7BCA21E04157E28 // Key to parent table: ieXMLDocument FROM $(FILE) (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesSent]);
MessagesReceived: LOAD MessageTypeCode, RecordCount, FileName(FILE) as Key // %Key_ieXMLDocument_C7BCA21E04157E28 // Key to parent table: ieXMLDocument FROM $(FILE) (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesReceived]);
ieXMLDocument:
LOAD date(date#(left([MIM602_DailySummaryReconciliationCount/StartTime],10),'YYYY-MM-DD')-1,'DD/MM/YYYY') as MM_DATE, [MIM602_DailySummaryReconciliationCount/StartTime] as StartTime, [MIM602_DailySummaryReconciliationCount/EndTime] as EndTime, [MessageHeader/MessageTypeCode], [MessageHeader/VersionNumber] as VersionNumber, [MessageHeader/MarketTimestamp] as MarketTimestamp, [MessageHeader/SenderID] as SenderID, [MessageHeader/RecipientID] as RecipientID, [MessageHeader/TxRefNbr] as TxRefNbr, FileName(FILE) as Key // %Key_ieXMLDocument_C7BCA21E04157E28 // Key for this table: ieXMLDocument FROM $(FILE) (XmlSimple, Table is [ieXMLDocument]); // End of [DSO_MIM602_1358321939079.xml] LOAD statements
