Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

My XML Data is avaliable in Excel how to into qlikview

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.

Capture.PNG

Thanks,

Tags (2)
8 Replies
Highlighted
MVP
MVP

Re: My XML Data is avaliable in Excel how to into qlikview

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

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

2017-03-15 #01.PNG

2017-03-15 #02.PNG

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.

2017-03-15 #3.PNG

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

Highlighted
Creator III
Creator III

Re: My XML Data is avaliable in Excel how to into qlikview

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,

Highlighted
Contributor III
Contributor III

Re: My XML Data is avaliable in Excel how to into qlikview

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

example.png

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

Highlighted
MVP
MVP

Re: My XML Data is avaliable in Excel how to into qlikview

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

Highlighted
Creator III
Creator III

Re: My XML Data is avaliable in Excel how to into qlikview

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.

Capture.PNG

Highlighted
Master
Master

Re: My XML Data is avaliable in Excel how to into qlikview

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

Highlighted
MVP
MVP

Re: My XML Data is avaliable in Excel how to into qlikview

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?

Highlighted
Creator III
Creator III

Re: My XML Data is avaliable in Excel how to into qlikview

Hi Adam,

   I am Looking for the similar one.

   Can you please post the output qlikview file??.

Thanks,