My XML Data is avaliable in Excel how to into qlikview

[jayaseelan]

Hi experts ,

     I have an scenario to load the My XML Data is avaliable in Excel how to into qlikview??

     My data be like following,

<ROOT>

  <Customers>

    <Customer CustomerID="C001" CustomerName="Arshad Ali">

      <Orders>

        <Order OrderID="10248" OrderDate="2012-07-04T00:00:00">

          <OrderDetail ProductID="10" Quantity="5" />

          <OrderDetail ProductID="11" Quantity="12" />

          <OrderDetail ProductID="42" Quantity="10" />

        </Order>

      </Orders>

      <Address> Address line 1, 2, 3</Address>

    </Customer>

    <Customer CustomerID="C002" CustomerName="Paul Henriot">

      <Orders>

        <Order OrderID="10245" OrderDate="2011-07-04T00:00:00">

          <OrderDetail ProductID="11" Quantity="12" />

          <OrderDetail ProductID="42" Quantity="10" />

        </Order>

      </Orders>

      <Address> Address line 5, 6, 7</Address>

    </Customer>

    <Customer CustomerID="C003" CustomerName="Carlos Gonzlez">

      <Orders>

        <Order OrderID="10283" OrderDate="2012-08-16T00:00:00">

          <OrderDetail ProductID="72" Quantity="3" />

        </Order>

      </Orders>

      <Address> Address line 1, 4, 5</Address>

    </Customer>

  </Customers>

</ROOT>

How to load this into Qlik? I need following output.

Thanks,

[petter]

Store your XML data in a file named for example CustomersOrders.XML

Then from the load script editor you can use [Table files] button:

Pressing Finish will generate something like this in you load script and you can just do a reload to read the XML into the two tables that will be correctly associated.

Lastly you can just create a Straight Table to get exactly what you want.

[jayaseelan]

Hi Petter,

         Thanks for your valuable reply, the problem is i should not convert excel data into xml file. i should load as it is whatever in the excel file.

Thanks,

[mfchmielowski]

Hi jayaseelan

As i understand your source file is in xml format and you need to show data from this xml in qlikview as they look like when you load them into excel.

petter-s wrote how you can load data from xml into qlikview. All you need to do next is to create straight table

I've changed OrderDate to YYYY-MM-DD format.

[petter]

So why do you mention XML when you have an Excel file? An Excel file can be read directly into QlikView... You will have to explain this much clearer.

Thanks

[jayaseelan]

Hi,

    Thank you so much for you fast reply, now i will explain clearly, forget all those things which we have discussed above.

      I have an qvd file which i have attached.  after loading that qvd file i should get following screen short as output.

[adamdavi3s]

I Had hoped you could simply spit out an xml and reload it but until this idea is implemented there is no hope as Qlik automatically adds quotes to the xml file

However you might be able to do this with a macro instead

Note the weird tmp and final tables are required to save the file without any hdeaders

source:
load '<?xml version=''1.0'' encoding=''utf-8'' ?>' &replace(XMLData,'"',chr(39)) as XMLData;
LOAD  XMLData

FROM
[.\XMLLoad.qvd](qvd);


tmp: NoConcatenate load * Resident source Where recno()=1;
let f1=Peek('XMLData', 0, 'tmp');



final: NoConcatenate load XMLData as '$(f1)' Resident source where recno()>1;
store final into testxml.xml(txt);




// Start of [testxml.xml] LOAD statements
OrderDetail:
LOAD ProductID,
    Quantity,
    %Key_Customer_E9B775906EAE5E96    // Key to parent table: ROOT/Customers/Customer
FROM [.\testxml.xml] (XmlSimple, Table is [ROOT/Customers/Customer/Orders/Order/OrderDetail]);


Customer:
LOAD CustomerID,
    CustomerName,
    Address,
    [Orders/Order/OrderID] as OrderID,
    [Orders/Order/OrderDate] as OrderDate,
    %Key_Customer_E9B775906EAE5E96    // Key for this table: ROOT/Customers/Customer
FROM [.\testxml.xml] (XmlSimple, Table is [ROOT/Customers/Customer]);
// End of [testxml.xml] LOAD statements

[petter]

The file you attached does not seem to be a valid QVD file. Where did you get it from - how was it generated?

Was it produced by QlikView or Qlik Sense and which version?

[jayaseelan]

Hi Adam,

   I am Looking for the similar one.

   Can you please post the output qlikview file??.

Thanks,