<LoadExtractRecords> - <LoadExtractRecord> <LoadID>16944907</LoadID> <ShipperLoadRef>100643477</ShipperLoadRef> <Status id="1071">COMPLETED</Status> - <Stops> - <Stop> <StopID>36243336</StopID> <Type id="1000">pick</Type> <SequenceNum>0</SequenceNum> - <ShippingStatusRecords> - <ShippingStatusRecord> <ShippingStatusID>27246960</ShippingStatusID> <EntityID>36243336</EntityID> <StatusCode id="EP">Pick-up No Earlier Than Date and/or Time</StatusCode> </ShippingStatusRecord> - <ShippingStatusRecord> <ShippingStatusID>27246960</ShippingStatusID> <EntityID>36243336</EntityID> <StatusCode id="LP">Pick-up No Later Than Date and/or Time</StatusCode> </ShippingStatusRecord> </ShippingStatusRecords> </Stop> - <Stop> <StopID>36243337</StopID> <Type id="1001">drop</Type> <SequenceNum>1</SequenceNum> - <ShippingStatusRecords> - <ShippingStatusRecord> <ShippingStatusID>27246961</ShippingStatusID> <EntityID>36243337</EntityID> <StatusCode id="LD">Deliver No Later Than Date and/or Time</StatusCode> </ShippingStatusRecord> - <ShippingStatusRecord> <ShippingStatusID>27246961</ShippingStatusID> <EntityID>36243337</EntityID> <StatusCode id="ED">Deliver No Earlier Than Date and/or Time</StatusCode> </ShippingStatusRecord> </Stop> </Stops> </LoadExtractRecord> </LoadExtractRecords>
If i use the XML wizard, I pull all the ShippingStatusID's but it wont link it to the LoadID. Those generated Key Fields the wizards make seem to be rather useless overall. I've done some reverse engneering of the code and came up with the below statement:
[Code]ShippingStatusRecord: LOAD DISTINCT LoadID, ShipperLoadRef, [Stops/Stop/ShippingStatusRecords/ShippingStatusRecord/ShippingStatusID] AS ShippingStatusID, [Stops/Stop/ShippingStatusRecords/ShippingStatusRecord/EntityID] AS EntityID, [Stops/Stop/ShippingStatusRecords/ShippingStatusRecord/StatusCode/id] AS StatusCodeID FROM $(vXMLPath) (XmlSimple, Table is [LoadExtractRecords/LoadExtractRecord])
[/Code]
The problem with this statement is for the one LoadID 1694490, I should be seeing a one to many relationship to the ShippingStatusIDs 27246960 and 27246961. However, Qlikview is only pulling the first ShippingStatusID 27246960 and leaving out ShippingstatusID 27246961. I've had some success with the above statment in circumstances where the ID for the subtable was the same for all instances, but in this case, it is different.
Anyone know a way I can get the LoadID and the ShippingStatusID's in their one to many relationship?
