Loading XML Data

[#1]

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?

[#2]

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;

[hector]

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