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

My XML Data is avaliable in Excel how to into qlikview

jayaseelan — Wed, 15 Mar 2017 11:45:06 GMT

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>

</Order>

</Orders>

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

</Customer>

</Order>

</Orders>

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

</Customer>

</Order>

</Orders>

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

</Customer>

</Customers>

</ROOT>

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

Thanks,

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

petter — Wed, 15 Mar 2017 12:11:46 GMT

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.

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

jayaseelan — Wed, 15 Mar 2017 12:45:36 GMT

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,

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

mfchmielowski — Wed, 15 Mar 2017 13:06:43 GMT

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.

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

petter — Wed, 15 Mar 2017 13:09:45 GMT

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

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

jayaseelan — Wed, 15 Mar 2017 13:19:26 GMT

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.

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

adamdavi3s — Wed, 15 Mar 2017 14:18:58 GMT

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

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

petter — Thu, 16 Mar 2017 01:10:14 GMT

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?

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

jayaseelan — Thu, 16 Mar 2017 04:44:41 GMT

Hi Adam,

I am Looking for the similar one.

Can you please post the output qlikview file??.

Thanks,