Loading XML Data

Tue, 13 Apr 2010 19:07:14 GMT

Hey Guys,

I'm having troubles loading data from an xml file. I tried the wizard but I didn't get the results I'm looking for. The XML file pulls in a list of sales opportunities. (see attached). There are two opportunities listed in this one.

no -> opportunity #
val -> field
FL % -> field value

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/6378.getRecords.xml:550:0]

So, the result I need is

Opportunity table
----------------------
POTENTIALID
SMOWNERID
Potential Owner
Amount
Potential Name
Closing Date
ECT....

Anyone have any ideas on this one?

Loading XML Data

Tue, 13 Apr 2010 21:14:18 GMT

Ok, I was able to come up with a solution. It's not the best idea but it works. I would love to hear if anyone has a better way.

TempOpportunities:

LOAD no, %Key_row_E3D0E1B2094648A7 FROM [my xml file here] (XmlSimple, Table is [response/result/Potentials/row]);

LEFT JOIN (TempOpportunities) LOAD val, FL%Table, %Key_row_E3D0E1B2094648A7 FROM [my xml file here] (XmlSimple, Table is [response/result/Potentials/row/FL]);

Opportunities: LOAD DISTINCT no, FL%Table AS PotentialOwner RESIDENT TempOpportunities WHERE val='Potential Owner';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS Amount RESIDENT TempOpportunities WHERE val='Amount';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS PotentialName RESIDENT TempOpportunities WHERE val='Potential Name';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS ClosingDate RESIDENT TempOpportunities WHERE val='Closing Date';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS AccountName RESIDENT TempOpportunities WHERE val='Account Name';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS Stage RESIDENT TempOpportunities WHERE val='Stage';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS Probability RESIDENT TempOpportunities WHERE val='Probability';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS Description RESIDENT TempOpportunities WHERE val='Description';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS ContactName RESIDENT TempOpportunities WHERE val='Contact Name';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS ExpectedRevenue RESIDENT TempOpportunities WHERE val='Expected Revenue';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS USAPracticeEngaged RESIDENT TempOpportunities WHERE val='USA Practice Engaged';

JOIN (Opportunities) LOAD DISTINCT no, FL%Table AS USACustomer RESIDENT TempOpportunities WHERE val='USA Customer';

DROP TABLE TempOpportunities;

Loading XML Data

hector — Tue, 13 Apr 2010 21:25:17 GMT

Hi, i've made a change in the "default" script, it works (at least for me xD)

is this the finale table what are you looking for?

See the picture

topic Loading XML Data in QlikView

Loading XML Data

Loading XML Data

Loading XML Data