在水壶中使用发票行拼合发票抬头
如果您的发票抬头包含多个值(发票编号,日期,位置)和具有多个值(产品,价格,税额)的未知数量的发票行,是否有办法将这些数据平铺到一行,这在发票行数量因发票而异的情况下得以延长?在水壶中使用发票行拼合发票抬头
输入例 -
{"InvoiceRecords": [{
"InvoiceDate": "8/9/2017 12:00:00 AM",
"InvoiceLocation": "002",
"InvoiceNumber": "2004085",
"InvoiceRecordHeaderDetails": [{
"InvNum": "2004085",
"Location": "002",
"InvDate": "8/9/2017 12:00:00 AM"
}],
"InvoiceRecordLineItemDetails": [{
"UniqueID": "3939934",
"InvNum": "2004085",
"LINEITEM": "1",
"CUSTID": "PREAA",
"DEPTID": "320306",
"PRODID": "088856",
"ProdDesc": "STATE UST",
"Unitprice": "0.003",
"QuantShare": "237.5",
"TaxRate": "7.25",
"taxamount": "0.05"
}],
"InvoiceTaxCodeDetails": [{
"InvNum": "2004085",
"LineItem": "1",
"UniqueID": "34",
"taxCode": "SALES TAX",
"taxrate": "7.25",
"maxtax": "0"
}]
}]}
我需要在同一行中的所有项目(允许有更比一个行项目和/或在给定的发票超过一个税法的项目。记录
输出示例(注意:在参考“_n”下面的发票行和税行可能未定量):
{"InvoiceRecords": [{
"InvoiceDate": "8/9/2017 12:00:00 AM",
"InvoiceLocation": "002",
"InvoiceNumber": "2004085",
"InvoiceRecordHeaderDetailsInvNum": "2004085",
"InvoiceRecordHeaderDetailsInvNumLocation": "002",
"InvoiceRecordHeaderDetailsInvNumInvDate": "8/9/2017 12:00:00 AM",
"InvoiceRecordLineItemDetailsUniqueID_1": "3939934",
"InvoiceRecordLineItemDetailsInvNum_1": "2004085",
"InvoiceRecordLineItemDetailsLINEITEM_1": "1",
"InvoiceRecordLineItemDetailsCUSTID_1": "PREAA",
"InvoiceRecordLineItemDetailsDEPTID_1": "320306",
"InvoiceRecordLineItemDetailsPRODID_1": "088856",
"InvoiceRecordLineItemDetailsProdDesc_1": "STATE UST",
"InvoiceRecordLineItemDetailsUnitprice_1": "0.003",
"InvoiceRecordLineItemDetailsQuantShare_1": "237.5",
"InvoiceRecordLineItemDetailsTaxRate_1": "7.25",
"InvoiceRecordLineItemDetailstaxamount_1": "0.05",
"InvoiceTaxCodeDetailsInvNum_1": "2004085",
"InvoiceTaxCodeDetailsLineItem_1": "1",
"InvoiceTaxCodeDetailsUniqueID_1": "34",
"InvoiceTaxCodeDetailstaxCode_1": "SALES TAX",
"InvoiceTaxCodeDetailstaxrate_1": "7.25",
"InvoiceTaxCodeDetailsmaxtax_1": "0",
"InvoiceRecordLineItemDetailsUniqueID_n": "3939934",
"InvoiceRecordLineItemDetailsInvNum_n": "2004085",
"InvoiceRecordLineItemDetailsLINEITEM_n": "1",
"InvoiceRecordLineItemDetailsCUSTID_n": "PREAA",
"InvoiceRecordLineItemDetailsDEPTID_n": "320306",
"InvoiceRecordLineItemDetailsPRODID_n": "088856",
"InvoiceRecordLineItemDetailsProdDesc_n": "STATE UST",
"InvoiceRecordLineItemDetailsUnitprice_n": "0.003",
"InvoiceRecordLineItemDetailsQuantShare_n": "237.5",
"InvoiceRecordLineItemDetailsTaxRate_n": "7.25",
"InvoiceRecordLineItemDetailstaxamount_n": "0.05",
"InvoiceTaxCodeDetailsInvNum_n": "2004085",
"InvoiceTaxCodeDetailsLineItem_n": "1",
"InvoiceTaxCodeDetailsUniqueID_n": "34",
"InvoiceTaxCodeDetailstaxCode_n": "SALES TAX",
"InvoiceTaxCodeDetailstaxrate_n": "7.25",
"InvoiceTaxCodeDetailsmaxtax_n": "0"
}]}
谢谢!
你有一个samples
目录中类似问题的例子,它位于你的spoon.bat附近。看一下samples/transformation/XML Add
并且在第一个choc中生存:他们做更复杂的事情,只是为了展示所有可能的事情。
在你的情况下,分割为Switch/Case
,输入流在标题,项目,并设法保持每个发票号码(稍后更多)。将三个流转换为JSON(使用JSON输出,或者更简单,使用Javascript
)。然后你通过发票号码Group by
项目。通过InvoiceNumber加入三个流程,为此我建议在页眉流中输入lookup stream
,然后在页脚流中输入其他lookup stream
。使用另一个javascript
并将数据视为字符串,可以使用格式为{header,[item],footer}的格式构建JSON行,您可以将Group by
连接为只有一行。
一些工作,但标准,除了棘手的部分获取物品和页脚上的发票号码,因为他们已从流中消失。为此,您可以使用javascript
保留值的事实,除非重新定义。添加一个新的启动脚本[右键单击标签顶部的Script1,添加一个副本,右键单击刚创建的Script1_0,并将其定义为启动脚本]。
在此启动脚本:
var PrevInvoiceNumber = -1;
在主脚本:
if(InvoiceNumber && PrevInvoiceNumber!=InvoiceNumber)
PrevInvoiceNumber = InvoiceNumber
我认为这是错误问题的正确答案。而且我想你更喜欢我纠正你解决问题的答案。但是我迟到了,你必须等待明天。基本上,它是相同的,除了你阅读3 JSON输入(头,项目,页脚)的数据,并保持InvoiceNr(这是可能的JSON输入)。使用序列号生成_n,设置键值对,将字段拆分为行,对它们进行排序并将它们分组到一个输出中。 – AlainD
三行示例将非常受欢迎! – AlainD
当然有。问题在于它们有很多,取决于数据的形状。对于输入,你有一行后面是任意数量的行。对于您希望每张发票只有一行的输出。你想要哪种类型的数据你的lineitem数据:一个字符串?数组?一个XML?一个JSON? – AlainD
@AlainD,我发表了一个例子。谢谢! – jag959