Loading Multiple XML and using Sum

paulm — Fri, 31 May 2013 12:03:16 GMT

Hi,

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

Re: Loading Multiple XML and using Sum

michael123 — Fri, 31 May 2013 13:54:59 GMT

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

NEXT FILE

topic Re: Loading Multiple XML and using Sum in QlikView

Loading Multiple XML and using Sum

Re: Loading Multiple XML and using Sum