Hello!
I am fairly new to Qlikview. I would like to load multiple XML files. The XML script (see below) consists of multiple tables wich I would like to join to one table so I can aggregate field [Price/Value] -table 1 by [Orders/Order/Project/code] - table 2.
I read several posts on this forum and created the enclosed QV-scipt (see below). Unfortunately this script is not running.
Can anyone provide some help? Thanks in advance!
Regards,
Job
QV-script
Let vPath = '.....'
for each vFile in FileList('$(vPath)\*.xml')
tmp_location: //Load first table
LOAD
RecNo() as tmp_loc_id,
[Quantity],
[Delivery/Date],
[Discount/Percentage] as Percentage,
[Price/type],
[Price/Value] as Waarde_somacalc,
[Price/Currency/code] as [Currency/code],
[Item/code],
[Item/type],
[Item/Description],
[Item/IsSalesItem] as IsSalesItem,
[Item/IsAssembledItem] as IsAssembledItem,
[Item/BOMs/BOM/code] as [BOM/code],
[Item/BOMs/BOM/versionnumber] as versionnumber,
[Item/BOMs/BOM/Linetype] as Linetype,
[Item/BOMs/BOM/MainVersion] as MainVersion,
[Item/BOMs/BOM/Quantity] as [BOM/Quantity],
[Item/BOMs/BOM/EffectiveDate] as EffectiveDate,
%Key_eExact_B6A84F3465E855A2, // Key to parent table: eExact
FROM [$(vFile)]
(XmlSimple, Table is [eExact/Orders/Order/OrderLine]);
tmp_line: //Load second table
LOAD
[num] as tmp_num,
[xmlns:xsi],
[xsi:noNamespaceSchemaLocation],
[Orders/Order/type] as [Order/type],
[Orders/Order/Description] as [Order/Description],
[Orders/Order/YourRef] as YourRef,
[Orders/Order/Selection/code] as [Selection/code],
[Orders/Order/PaymentMethod/code] as [PaymentMethod/code],
[Orders/Order/Warehouse/code] as [Warehouse/code],
[Orders/Order/Project/code] as project,
[Orders/Order/Project/code] as project_somacalc,
[Orders/Order/InvoiceTo/Debtor/code] as [InvoiceTo/Debtor/code],
[Orders/Order/DeliverTo/Date] as [DeliverTo/Date],
[Orders/Order/DeliverTo/Debtor/code] as [DeliverTo/Debtor/code],
[Orders/Order/OrderedBy/Date] as [OrderedBy/Date],
[Orders/Order/OrderedBy/Debtor/code] as [OrderedBy/Debtor/code],
%Key_eExact_B6A84F3465E855A2 // Key for this table: eExact
from [$(vFile)]
(XmlSimple, Table is [eExact]);
JOIN(tmp_location) //Join table
LOAD tmp_num, alt(Lookup('tmp_loc_id','line_start_num',tmp_num,'tmp_location'),peek(tmp_loc_id)) as tmp_loc_id //VLOOPUP tmp_loc_id & TMP_NUM
Resident tmp_line;
line:
LOAD tmp_loc_id as loc_id,
tmp_num as num,
//Velden tabel 1
(Quantity),
[Delivery/Date],
[Discount/Percentage] as Percentage,
[Price/type],
[Price/Value] as Waarde_somacalc,
[Price/Currency/code] as [Currency/code],
[Item/code],
[Item/type],
[Item/Description],
[Item/IsSalesItem] as IsSalesItem,
[Item/IsAssembledItem] as IsAssembledItem,
[Item/BOMs/BOM/code] as [BOM/code],
[Item/BOMs/BOM/versionnumber] as versionnumber,
[Item/BOMs/BOM/Linetype] as Linetype,
[Item/BOMs/BOM/MainVersion] as MainVersion,
[Item/BOMs/BOM/Quantity] as [BOM/Quantity],
[Item/BOMs/BOM/EffectiveDate] as EffectiveDate,
%Key_eExact_B6A84F3465E855A2, // Key to parent table: eExact
//Velden tabel 2
[xsi:noNamespaceSchemaLocation],
[Orders/Order/type] as [Order/type],
[Orders/Order/Description] as [Order/Description],
[Orders/Order/YourRef] as YourRef,
[Orders/Order/Selection/code] as [Selection/code],
[Orders/Order/PaymentMethod/code] as [PaymentMethod/code],
[Orders/Order/Warehouse/code] as [Warehouse/code],
[Orders/Order/Project/code] as project,
[Orders/Order/Project/code] as project_somacalc,
[Orders/Order/InvoiceTo/Debtor/code] as [InvoiceTo/Debtor/code],
[Orders/Order/DeliverTo/Date] as [DeliverTo/Date],
[Orders/Order/DeliverTo/Debtor/code] as [DeliverTo/Debtor/code],
[Orders/Order/OrderedBy/Date] as [OrderedBy/Date],
[Orders/Order/OrderedBy/Debtor/code] as [OrderedBy/Debtor/code],
%Key_eExact_B6A84F3465E855A2 // Key for this table: eExact
Resident tmp_line;
Drop Tables tmp_location, tmp_line;
NEXT
XML
<eExact>
<xmlns:xsi>values (1)</xmlns:xsi>
<xsi:noNamespaceSchemaLocation>values (1)</xsi:noNamespaceSchemaLocation>
<Orders>
<Order>
<type>values (1)</type>
<Description>values (1)</Description>
<YourRef>values (1)</YourRef>
<OrderedBy>
<Debtor>
<code>values (1)</code>
</Debtor>
<Date>values (1)</Date>
</OrderedBy>
<DeliverTo>
<Date>values (1)</Date>
<Debtor>
<code>values (1)</code>
</Debtor>
</DeliverTo>
<InvoiceTo>
<Debtor>
<code>values (1)</code>
</Debtor>
</InvoiceTo>
<Project>
<code>values (1)</code>
</Project>
<Warehouse>
<code>values (1)</code>
</Warehouse>
<PaymentMethod>
<code>values (1)</code>
</PaymentMethod>
<Selection>
<code>values (1)</code>
</Selection>
<OrderLine>
<lineNo>values (9)</lineNo>
<Description>values (9)</Description>
<Quantity>values (9)</Quantity>
<Unit>
<unit>values (18)</unit>
<type>values (18)</type>
<active>values (18)</active>
</Unit> (9 sibling groups)
<Item>
<code>values (9)</code>
<type>values (9)</type>
<Description>values (9)</Description>
<IsSalesItem>values (9)</IsSalesItem>
<IsAssembledItem>values (9)</IsAssembledItem>
<BOMs>
<BOM>
<code>values (9)</code>
<versionnumber>values (9)</versionnumber>
<Description></Description>
<Linetype>values (9)</Linetype>
<MainVersion>values (9)</MainVersion>
<Quantity>values (9)</Quantity>
<EffectiveDate>values (9)</EffectiveDate>
<BOMLine>
<type>values (108)</type>
<sequencenumber>values (108)</sequencenumber>
<Item>
<code>values (108)</code>
<Unit>
<unit>values (99)</unit>
<type>values (99)</type>
<active>values (99)</active>
</Unit>
</Item>
<Quantity>values (108)</Quantity>
<Condition>values (108)</Condition>
<BackFlush>values (108)</BackFlush>
<Operation>values (108)</Operation>
<Step>values (108)</Step>
</BOMLine> (9 sibling groups)
</BOM>
</BOMs>
</Item>
<Price>
<type>values (9)</type>
<Value>values (9)</Value>
<Currency>
<code>values (9)</code>
</Currency>
</Price>
<Discount>
<Percentage>values (9)</Percentage>
</Discount>
<Delivery>
<Date>values (9)</Date>
</Delivery>
</OrderLine> (1 sibling groups)
</Order>
</Orders>
</eExact>
