0 Replies Latest reply: Sep 19, 2016 8:05 AM by Rolf Becker RSS

    Processing of 1000 xml-files in a loop: QlikView hangs

    Rolf Becker

      Hello everyone,

       

      I have to make a report for vehicle orders. In every single xml-file there is one order for one vehicle.

      The xml-files have to be processed in a loop and they all have the same structure and fields.

      I have tested my coding with 3 xml-files and I get a correct result. However, the folder contains 1000 xml-files which have to be handled. After more than 100 files I get no response from QlikView, it seems that QlikView hangs or crashes.

      The coding includes 3 load statements for the tables included in the xml-file which are linked by a join in my coding.

      I have attached the coding, the table structure (reading 3 xml-files) and the result.

      Is the coding incorrect or is it not possible to handle such a big number of xml-files?

      Thank you for your help,

       

      Rolf

       

       

      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;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
      SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
      SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
      SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
      SET FirstWeekDay=0;
      SET BrokenWeeks=0;
      SET ReferenceDay=4;
      SET FirstMonthOfYear=1;
      SET CollationLocale='de-DE';

      sub ScanFolder(Root)

      for each FileExtension in 'xml'

      for each FoundFile in filelist( Root & '\*.' & FileExtension)

      Order:
      LOAD [OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount] as [TotalAmount/MonetaryValue/MonetaryAmount],
      [OrderSummary/TotalAmount/MonetaryValue/Currency/CurrencyCoded] as [MonetaryValue/Currency/CurrencyCoded],
      [OrderHeader/OrderIssueDate] as OrderIssueDate,
      [OrderHeader/ReleaseNumber] as ReleaseNumber,
      [OrderHeader/OrderHeaderNote] as OrderHeaderNote,
      [OrderHeader/ListOfStructuredNote/StructuredNote/GeneralNote] as [StructuredNote/GeneralNote],
      [OrderHeader/ListOfStructuredNote/StructuredNote/NoteID] as [StructuredNote/NoteID],
      [OrderHeader/OrderPaymentInstructions/PaymentInstructions/PaymentMethod/PaymentMeanCoded] as PaymentMeanCoded,
      [OrderHeader/OrderPaymentInstructions/PaymentInstructions/PaymentMethod/PaymentMeanCodedOther] as PaymentMeanCodedOther,
      [OrderHeader/OrderPaymentInstructions/PaymentInstructions/PaymentTerms/PaymentTerm/PaymentTermCoded] as PaymentTermCoded,
      [ContractID/Identifier/Ident],
      [OrderHeader/OrderReferences/AccountCode/Reference/RefNum] as [AccountCode/Reference/RefNum],
      [OrderHeader/OrderNumber/BuyerOrderNumber] as BuyerOrderNumber,
      %Key_Order_A113820B1D524340
      from [$(FoundFile)] (XmlSimple, Table is [Order]) ;

         
      inner join
      ItemDetail:
      LOAD LineItemNote,
      [DeliveryDetail/ListOfScheduleLine/ScheduleLine/RequestedDeliveryDate] as RequestedDeliveryDate,
      [DeliveryDetail/ListOfScheduleLine/ScheduleLine/Quantity/QuantityValue] as [ScheduleLine/Quantity/QuantityValue],
      [BaseItemDetail/LineItemType/LineItemTypeCoded] as LineItemTypeCoded,
      [BaseItemDetail/LineItemType/LineItemTypeCodedOther] as LineItemTypeCodedOther,
      [BaseItemDetail/LineItemNum/BuyerLineItemNum] as BuyerLineItemNum,
      %Key_Order_A113820B1D524340,    // Key to parent table: Order
          %Key_ItemDetail_BD2A7FC403DB35C4    // Key for this table: Order/OrderDetail/ListOfItemDetail/ItemDetail
      FROM [$(FoundFile)] (XmlSimple, Table is [Order/OrderDetail/ListOfItemDetail/ItemDetail])
      WHERE [BaseItemDetail/LineItemNum/BuyerLineItemNum] = '00010';

      inner join
      ReferenceCoded:
      LOAD ReferenceTypeCoded,
      [SupportingReference/Reference/RefNum],
      [PrimaryReference/Reference/RefNum],
      %Key_ItemDetail_BD2A7FC403DB35C4    // Key to parent table: Order/OrderDetail/ListOfItemDetail/ItemDetail
      FROM [$(FoundFile)] (XmlSimple, Table is [Order/OrderDetail/ListOfItemDetail/ItemDetail/BaseItemDetail/ListOfItemReferences/ListOfReferenceCoded/ReferenceCoded])
      WHERE ReferenceTypeCoded = 'CustomersUnitInventoryNumber' ;                 

       

        next FoundFile

      next FileExtension

      end sub

      Call ScanFolder('D:\data\Qlik\INCOME_ORDERS') ;


      Structure of the tables (when processing 3 xml files):

       

       

       


      correct result when processing 3 xml files (only some columns are shown):