How to load xml data correctly

[#1]

Hi,

I have an xml data file which I want to load into qlikview. However the structure of the xml file is not how I want it in my qlikview document. It splits up two data fields that I want together. How do I link the fields correctly together?

Please advice,

Coen

[#2]

Fantastic, works like a charm. Can't mark two answers as answered but you definitely did! Thanks for you efforts and time!

[#3]

It almost works like a charm then . The sale dates are no longer coming through correctly. When I load all the files I only get three dates for a period of 18 weeks .

For the test files it looks like it's only taking the dates for one file and skips the rest. For the test file the dates should be:

1-9-2013, 22-9-2013 and 23-9-2013.

Currently it only takes 22 and 23. In a document where I loaded some additional files also, only 3 dates show up there (should be a lot more)

[rbecher]

Right, we have to pin the salesdate to the line:

FOR Each File in filelist ('testfile*.xml')

tmp_location:
LOAD [location/gln] as tmp_gln,
    [location/qualifier] as tmp_qualifier,
    date(date#(left([location/salesdate],10),'YYYY-MM-DD')) as tmp_salesdate,
    [lines/line/num] as line_start_num
FROM [$(File)] (XmlSimple, Table is [reflecta/message/locations]);

tmp_line:
LOAD [num] as tmp_num,
    [gtin] as tmp_gtin,
    [loccode] as tmp_loccode,
    [pricenet] as tmp_pricenet,
    [salesqty] as tmp_salesqty
FROM [$(File)] (XmlSimple, Table is [reflecta/message/locations/lines/line]);

JOIN(tmp_line) LOAD tmp_num, 
alt(Lookup('tmp_gln','line_start_num',tmp_num,'tmp_location'),peek(tmp_gln)) as tmp_gln,
alt(Lookup('tmp_salesdate','line_start_num',tmp_num,'tmp_location'),peek(tmp_salesdate)) as tmp_salesdate 
Resident tmp_line;

location:
LOAD tmp_gln as gln,
tmp_qualifier as qualifier
Resident tmp_location 
Where Not Exists(gln, tmp_gln);

line:
LOAD tmp_gln as gln,
tmp_num as num,
tmp_salesdate as salesdate,
tmp_gtin as gtin,
tmp_loccode as loccode,
tmp_pricenet as pricenet,
tmp_salesqty as salesqty
Resident tmp_line;

Drop Tables tmp_location, tmp_line;

NEXT

[AtilaH]

I have an xml where I am struggling with a similar issue, I have tried a various versions of your code and cant seem to get the correct output, certain fields are assigned to each and every claim line

<?xml version="1.0" encoding="UTF-8"?><dsSORequest xmlns:datetime="http://exslt.org/dates-and-times"><ttDataInterfaceHeader><cInterfaceName>ClaimSwitchout</cInterfaceName><iInterfaceVersion>1</iInterfaceVersion><iStandardsVersion>1</iStandardsVersion><tDateTime>2019-01-28T08:45:23+02:00</tDateTime></ttDataInterfaceHeader><submitClaimsResponse><identifier>MIPSO</identifier><sequence>0001</sequence><reqType>C</reqType><transType>X</transType><header><requestOriginator><dateOfService>2019-01-28</dateOfService><npi>6083536</npi><orderNumber>00100874</orderNumber></requestOriginator><responseStatus>ACCEPTED</responseStatus></header><memDep><schemeCode>0</schemeCode><memNum>1000003</memNum><depNum>00</depNum><depId>?</depId><depLastName>HARPER</depLastName><depFirstName>ATILA</depFirstName><depBirthDate>1988-10-09</depBirthDate><depGender>M</depGender></memDep><claims><scriptNum>00100874</scriptNum><uniqueNum>608353600100874</uniqueNum><prescriber>1515357</prescriber><payee>D</payee><standin>false</standin><claim><uniqueSeq>0001</uniqueSeq><statusCode>REJECTED</statusCode><authorisationNumber>1083589451</authorisationNumber><rxRefNumber>?</rxRefNumber><tariffCode>MEDS</tariffCode><nappiCode>704606001</nappiCode><primDiag>Z76.9</primDiag><benInd>A</benInd><quantity>30</quantity><prescription><fillNumber>0</fillNumber><compoundCode>0</compoundCode><ingredCost>0</ingredCost></prescription><financial><nettAmount>57.89</nettAmount><schSurcharge>0</schSurcharge><discAmount>0.00</discAmount><claimed>66.36</claimed><dispFee>13.27</dispFee><grossAmount>0</grossAmount><patientPayAmount>0</patientPayAmount><ingredientCostPaid>0</ingredientCostPaid><dispensingFeePaid>0</dispensingFeePaid><pharmacyAmount>0</pharmacyAmount><accumulatedDeductableAmount>0</accumulatedDeductableAmount><remainingDeductableAmount>0</remainingDeductableAmount><remainingBenefitAmount>0</remainingBenefitAmount><appliedDeductableAmount>0</appliedDeductableAmount><copayAmount>0</copayAmount><productSelectionAmount>0</productSelectionAmount><exceededBenefitAmount>0</exceededBenefitAmount><salesTaxAmount>0</salesTaxAmount></financial></claim><claim><uniqueSeq>0002</uniqueSeq><statusCode>REJECTED</statusCode><rejectMessage><code>DFLT-0001(31)</code><classification>?</classification><description>?</description></rejectMessage><rejectMessage><code>?</code><classification>?</classification><description>31</description></rejectMessage><authorisationNumber>1083589452</authorisationNumber><rxRefNumber>?</rxRefNumber><tariffCode>MEDS</tariffCode><nappiCode>841552003</nappiCode><primDiag>Z76.9</primDiag><benInd>A</benInd><quantity>10</quantity><prescription><fillNumber>0</fillNumber><compoundCode>0</compoundCode><ingredCost>0</ingredCost></prescription><financial><nettAmount>335.38</nettAmount><schSurcharge>0</schSurcharge><discAmount>0.00</discAmount><claimed>382.33</claimed><dispFee>25.00</dispFee><grossAmount>0</grossAmount><patientPayAmount>0</patientPayAmount><ingredientCostPaid>0</ingredientCostPaid><dispensingFeePaid>0</dispensingFeePaid><pharmacyAmount>0</pharmacyAmount><accumulatedDeductableAmount>0</accumulatedDeductableAmount><remainingDeductableAmount>0</remainingDeductableAmount><remainingBenefitAmount>0</remainingBenefitAmount><appliedDeductableAmount>0</appliedDeductableAmount><copayAmount>0</copayAmount><productSelectionAmount>0</productSelectionAmount><exceededBenefitAmount>0</exceededBenefitAmount><salesTaxAmount>0</salesTaxAmount></financial></claim><claim><uniqueSeq>0003</uniqueSeq><statusCode>REJECTED</statusCode><rejectMessage><code>DFLT-0001(31)</code><classification>?</classification><description>?</description></rejectMessage><rejectMessage><code>?</code><classification>?</classification><description>31</description></rejectMessage><authorisationNumber>1083589453</authorisationNumber><rxRefNumber>?</rxRefNumber><tariffCode>MEDS</tariffCode><nappiCode>704606001</nappiCode><primDiag>Z76.9</primDiag><benInd>A</benInd><quantity>30</quantity><prescription><fillNumber>0</fillNumber><compoundCode>0</compoundCode><ingredCost>0</ingredCost></prescription><financial><nettAmount>57.89</nettAmount><schSurcharge>0</schSurcharge><discAmount>0.00</discAmount><claimed>66.36</claimed><dispFee>13.27</dispFee><grossAmount>0</grossAmount><patientPayAmount>0</patientPayAmount><ingredientCostPaid>0</ingredientCostPaid><dispensingFeePaid>0</dispensingFeePaid><pharmacyAmount>0</pharmacyAmount><accumulatedDeductableAmount>0</accumulatedDeductableAmount><remainingDeductableAmount>0</remainingDeductableAmount><remainingBenefitAmount>0</remainingBenefitAmount><appliedDeductableAmount>0</appliedDeductableAmount><copayAmount>0</copayAmount><productSelectionAmount>0</productSelectionAmount><exceededBenefitAmount>0</exceededBenefitAmount><salesTaxAmount>0</salesTaxAmount></financial></claim><claim><uniqueSeq>0004</uniqueSeq><statusCode>REJECTED</statusCode><rejectMessage><code>DFLT-0001(31)</code><classification>?</classification><description>?</description></rejectMessage><rejectMessage><code>?</code><classification>?</classification><description>31</description></rejectMessage><authorisationNumber>1083589454</authorisationNumber><rxRefNumber>?</rxRefNumber><tariffCode>MEDS</tariffCode><nappiCode>841552003</nappiCode><primDiag>Z76.9</primDiag><benInd>A</benInd><quantity>10</quantity><prescription><fillNumber>0</fillNumber><compoundCode>0</compoundCode><ingredCost>0</ingredCost></prescription><financial><nettAmount>335.38</nettAmount><schSurcharge>0</schSurcharge><discAmount>0.00</discAmount><claimed>382.33</claimed><dispFee>25.00</dispFee><grossAmount>0</grossAmount><patientPayAmount>0</patientPayAmount><ingredientCostPaid>0</ingredientCostPaid><dispensingFeePaid>0</dispensingFeePaid><pharmacyAmount>0</pharmacyAmount><accumulatedDeductableAmount>0</accumulatedDeductableAmount><remainingDeductableAmount>0</remainingDeductableAmount><remainingBenefitAmount>0</remainingBenefitAmount><appliedDeductableAmount>0</appliedDeductableAmount><copayAmount>0</copayAmount><productSelectionAmount>0</productSelectionAmount><exceededBenefitAmount>0</exceededBenefitAmount><salesTaxAmount>0</salesTaxAmount></financial></claim></claims></submitClaimsResponse></dsSORequest>

[AtilaH]

HI, 

Could you provide a XML and QV model to show how this works, I have been struggling with and XML file of my own to load and have not managed to get the code to work

Regards

A