主页spacer>Mergemill 标签指南>spacer自动化数据处理

spacer

内容插入

算术表达式

循环

分支

系统值

统计函数

区段

范例

spacer

这个看似很长的例子实际上并不复杂,只因美国有许多州份。它演示了如何使用占位栏、表达式、循环、分支结构和变量等标签,及如何运用相对简易的模板脚本逻辑去快速解决问题,以避免使用耗费大量时间和精力的其他方法。


要解决的难题

基於税务方面的原因,美国的一间直销公司需要为每位客户提供一份为其寄发所有邮件的分布报告。每个客户的原始数据都包含在一个以客户代码命名的文件中(如 "abc123.csv")。每份该等文件内皆有两列类似下列的数据:

 

"InvoiceNum","State"
"12345","MO"
"12345","MI"
"12345","MI"
"12345","NY"
"12348","MI"
"12348","NY"
"12348","NY"
"12348","NY"
"12348","NY"
"12349","MI"
...

他们最终要为每一个客户提供的文件内容如下:

 

"InvoiceNum","AL",...,"MI","MN","MS","MO",...,"NY",...
"12345","0",...,"2","0","0","1",...,"1",...
"12348","0",...,"1","0","0","0",...,"4",...
"12349",...
...

每份完成的客户报告名称以「rpt_」开始,其後是客户代码,像 "rpt_abc123.csv"。

space
解决方案

我们需要设置两个作业项目:

*

作业 1 统计每张发票 (InvoiceNum) 记录於每个州份 (State) 所寄发的邮件

*

作业 2 采用作业 1 的结果,并把所有资料列成一个表格输出

 


** 作业 1

1.

创建一个「作业」文件夹,并於其内添加这些子文件夹:

 

*

「数据源」文件夹包含所有客户的数据源文件

 

*

「输出」文件夹储存完成後的客户报告

 

*

「模板」文件夹包含所有作业模板文件

2.

在「数据源」文件夹内添加「作业1输出」子文件夹。

3.

这项作业所生成的文件将被储存到「数据源」>「作业1输出」文件夹,所以你需要指定它为「输出文件夹路径」。

 

*

每个输出文件命名会以客户代码为首,其後是发票编号,中间用下划线分隔,如 "abc123_12345.csv"

 

*

每个输出文件皆包含一个标题行 ("ClientCode","InvoiceNum","AL","AK",...[至包括所有州份]),接着是一张发票的一行相关数据,如 "abc123","12345","0",...,"2","0","0","1",...,"1",...

4.

此作业的模板只包含这两行脚本 (蓝色标签只为提高可读性):

 

L1

"ClientCode","InvoiceNum","AL","AK","AZ","AR","CA","CO","CT","DC","DE","FL","GA","HI","ID",
"IL","IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NM","NY",
"NJ","NC","ND","OH","OK","OR","PA","RI","SC","SD","TN","TX","UT","VT","VA","WA","WV","WI","WY"

 

L2

<?Hd?><?[State]?><?/Hd?><?Vr:vAL?>0<?/Vr?><?Vr:vAK?>0<?/Vr?><?Vr:vAZ?>0<?/Vr?><?Vr:vAR?>0<?/Vr?><?Vr:vCA?>0<?/Vr?><?Vr:vCO?>0<?/Vr?><?Vr:vCT?>0<?/Vr?><?Vr:vDC?>0<?/Vr?><?Vr:vDE?>0<?/Vr?><?Vr:vFL?>0<?/Vr?><?Vr:vGA?>0<?/Vr?><?Vr:vHI?>0<?/Vr?><?Vr:vID?>0<?/Vr?><?Vr:vIL?>0<?/Vr?><?Vr:vIN?>0<?/Vr?><?Vr:vIA?>0<?/Vr?><?Vr:vKS?>0<?/Vr?><?Vr:vKY?>0<?/Vr?><?Vr:vLA?>0<?/Vr?><?Vr:vME?>0<?/Vr?><?Vr:vMD?>0<?/Vr?><?Vr:vMA?>0<?/Vr?><?Vr:vMI?>0<?/Vr?><?Vr:vMN?>0<?/Vr?><?Vr:vMS?>0<?/Vr?><?Vr:vMO?>0<?/Vr?><?Vr:vMT?>0<?/Vr?><?Vr:vNE?>0<?/Vr?><?Vr:vNV?>0<?/Vr?><?Vr:vNH?>0<?/Vr?><?Vr:vNM?>0<?/Vr?><?Vr:vNY?>0<?/Vr?><?Vr:vNJ?>0<?/Vr?><?Vr:vNC?>0<?/Vr?><?Vr:vND?>0<?/Vr?><?Vr:vOH?>0<?/Vr?><?Vr:vOK?>0<?/Vr?><?Vr:vOR?>0<?/Vr?><?Vr:vPA?>0<?/Vr?><?Vr:vRI?>0<?/Vr?><?Vr:vSC?>0<?/Vr?><?Vr:vSD?>0<?/Vr?><?Vr:vTN?>0<?/Vr?><?Vr:vTX?>0<?/Vr?><?Vr:vUT?>0<?/Vr?><?Vr:vVT?>0<?/Vr?><?Vr:vVA?>0<?/Vr?><?Vr:vWA?>0<?/Vr?><?Vr:vWV?>0<?/Vr?><?Vr:vWI?>0<?/Vr?><?Vr:vWY?>0<?/Vr?><?Loop?><?Vr:vInvNum?><?[InvoiceNum]?><?/Vr?><?StartCase?><?Case([State]=AL)?><?Vr:vAL?><?(vAL + 1)?><?/Vr?><?Case([State]=AK)?><?Vr:vAK?><?(vAK + 1)?><?/Vr?><?Case([State]=AZ)?><?Vr:vAZ?><?(vAZ + 1)?><?/Vr?><?Case([State]=AR)?><?Vr:vAR?><?(vAR + 1)?><?/Vr?><?Case([State]=CA)?><?Vr:vCA?><?(vCA + 1)?><?/Vr?><?Case([State]=CO)?><?Vr:vCO?><?(vCO + 1)?><?/Vr?><?Case([State]=CT)?><?Vr:vCT?><?(vCT + 1)?><?/Vr?><?Case([State]=DC)?><?Vr:vDC?><?(vDC + 1)?><?/Vr?><?Case([State]=DE)?><?Vr:vDE?><?(vDE + 1)?><?/Vr?><?Case([State]=FL)?><?Vr:vFL?><?(vFL + 1)?><?/Vr?><?Case([State]=GA)?><?Vr:vGA?><?(vGA + 1)?><?/Vr?><?Case([State]=HI)?><?Vr:vHI?><?(vHI + 1)?><?/Vr?><?Case([State]=ID)?><?Vr:vID?><?(vID + 1)?><?/Vr?><?Case([State]=IL)?><?Vr:vIL?><?(vIL + 1)?><?/Vr?><?Case([State]=IN)?><?Vr:vIN?><?(vIN + 1)?><?/Vr?><?Case([State]=IA)?><?Vr:vIA?><?(vIA + 1)?><?/Vr?><?Case([State]=KS)?><?Vr:vKS?><?(vKS + 1)?><?/Vr?><?Case([State]=KY)?><?Vr:vKY?><?(vKY + 1)?><?/Vr?><?Case([State]=LA)?><?Vr:vLA?><?(vLA + 1)?><?/Vr?><?Case([State]=MA)?><?Vr:vMA?><?(vMA + 1)?><?/Vr?><?Case([State]=MD)?><?Vr:vMD?><?(vMD + 1)?><?/Vr?><?Case([State]=ME)?><?Vr:vME?><?(vME + 1)?><?/Vr?><?Case([State]=MI)?><?Vr:vMI?><?(vMI + 1)?><?/Vr?><?Case([State]=MN)?><?Vr:vMN?><?(vMN + 1)?><?/Vr?><?Case([State]=MO)?><?Vr:vMO?><?(vMO + 1)?><?/Vr?><?Case([State]=MS)?><?Vr:vMS?><?(vMS + 1)?><?/Vr?><?Case([State]=MT)?><?Vr:vMT?><?(vMT + 1)?><?/Vr?><?Case([State]=NE)?><?Vr:vNE?><?(vNE + 1)?><?/Vr?><?Case([State]=NV)?><?Vr:vNV?><?(vNV + 1)?><?/Vr?><?Case([State]=NH)?><?Vr:vNH?><?(vNH + 1)?><?/Vr?><?Case([State]=NM)?><?Vr:vNM?><?(vNM + 1)?><?/Vr?><?Case([State]=NY)?><?Vr:vNY?><?(vNY + 1)?><?/Vr?><?Case([State]=NJ)?><?Vr:vNJ?><?(vNJ + 1)?><?/Vr?><?Case([State]=NC)?><?Vr:vNC?><?(vNC + 1)?><?/Vr?><?Case([State]=ND)?><?Vr:vND?><?(vND + 1)?><?/Vr?><?Case([State]=OH)?><?Vr:vOH?><?(vOH + 1)?><?/Vr?><?Case([State]=OK)?><?Vr:vOK?><?(vOK + 1)?><?/Vr?><?Case([State]=OR)?><?Vr:vOR?><?(vOR + 1)?><?/Vr?><?Case([State]=PA)?><?Vr:vPA?><?(vPA + 1)?><?/Vr?><?Case([State]=RI)?><?Vr:vRI?><?(vRI + 1)?><?/Vr?><?Case([State]=SC)?><?Vr:vSC?><?(vSC + 1)?><?/Vr?><?Case([State]=SD)?><?Vr:vSD?><?(vSD + 1)?><?/Vr?><?Case([State]=TN)?><?Vr:vTN?><?(vTN + 1)?><?/Vr?><?Case([State]=TX)?><?Vr:vTX?><?(vTX + 1)?><?/Vr?><?Case([State]=UT)?><?Vr:vUT?><?(vUT + 1)?><?/Vr?><?Case([State]=VT)?><?Vr:vVT?><?(vVT + 1)?><?/Vr?><?Case([State]=VA)?><?Vr:vVA?><?(vVA + 1)?><?/Vr?><?Case([State]=WA)?><?Vr:vWA?><?(vWA + 1)?><?/Vr?><?Case([State]=WV)?><?Vr:vWV?><?(vWV + 1)?><?/Vr?><?Case([State]=WI)?><?Vr:vWI?><?(vWI + 1)?><?/Vr?><?Case([State]=WY)?><?Vr:vWY?><?(vWY + 1)?><?/Vr?><?EndCase?><?If(Same[InvoiceNum]{2})?><?Else?><?Exit?><?EndIf?><?EndLoop?>"<?[ClientCode]?>","<?vInvNum?>","<?vAL@#,###,##0?>","<?vAK@#,###,##0?>","<?vAZ@#,###,##0?>","<?vAR@#,###,##0?>","<?vCA@#,###,##0?>","<?vCO@#,###,##0?>","<?vCT@#,###,##0?>","<?vDC@#,###,##0?>","<?vDE@#,###,##0?>","<?vFL@#,###,##0?>","<?vGA@#,###,##0?>","<?vHI@#,###,##0?>","<?vID@#,###,##0?>","<?vIL@#,###,##0?>","<?vIN@#,###,##0?>","<?vIA@#,###,##0?>","<?vKS@#,###,##0?>","<?vKY@#,###,##0?>","<?vLA@#,###,##0?>","<?vME@#,###,##0?>","<?vMD@#,###,##0?>","<?vMA@#,###,##0?>","<?vMI@#,###,##0?>","<?vMN@#,###,##0?>","<?vMS@#,###,##0?>","<?vMO@#,###,##0?>","<?vMT@#,###,##0?>","<?vNE@#,###,##0?>","<?vNV@#,###,##0?>","<?vNH@#,###,##0?>","<?vNM@#,###,##0?>","<?vNY@#,###,##0?>","<?vNJ@#,###,##0?>","<?vNC@#,###,##0?>","<?vND@#,###,##0?>","<?vOH@#,###,##0?>","<?vOK@#,###,##0?>","<?vOR@#,###,##0?>","<?vPA@#,###,##0?>","<?vRI@#,###,##0?>","<?vSC@#,###,##0?>","<?vSD@#,###,##0?>","<?vTN@#,###,##0?>","<?vTX@#,###,##0?>","<?vUT@#,###,##0?>","<?vVT@#,###,##0?>","<?vVA@#,###,##0?>","<?vWA@#,###,##0?>","<?vWV@#,###,##0?>","<?vWI@#,###,##0?>","<?vWY@#,###,##0?>"

5.

占位栏的数据馈送:

 

*

选取「自动生成文字」为客户代码 [ClientCode] 的数据馈送源,然後於自动生成文本设定内选择「数据源档名」

 

*

选取「文件夹」为发票编号 [InvoiceNum] 及州份 [State] 的数据馈送源,然後选用「数据源」文件夹。当然,你要先於「数据源:文件夹」页面内添加「数据源」文件夹为一个源文件夹路径设定

6.

设定输出档名为「数据源档名」+"_"+[InvoiceNum]+".csv"。

7.

设定按 [InvoiceNum] 将数据馈送排序。

 

作业1模板说明

1.

模板指示 Mergemill Pro 首先创建一个包括所有占位栏名的标题行(L1),然後初始化作统计用的变量,循环计算邮件数量,最後添加一行当前发票在各州份经格式化的最终邮件寄发量。模板至此完成脚本,文档会被生成至预先已设定的位置。

2.

L2 先设置一个循环外州份占位栏 [State] 於隐藏区段之内,因为

 

*

我们使用「数据源档名」作为客户代码占位栏 [ClientCode] 的数据及输出档名的组成部份。

 

*

Mergemill 总是以模板内首个循环外占位栏去确定当前页面的源档名。

 

*

我们不能用 [InvoiceNum] 作为这个循环外占位栏,因为我们还使用它的数据值来建立输出档名,而 Mergemill 总是使用占位栏的「起始数据值」作为档名部份。

 

*

一个循环占位栏的起始数据值是其当前数据流中的第一个值,而在此例的情况下则是源文档中的第一个值,即是 "abc123.csv" 內的 "12345"。这样,为同一客户生成的所有发票输出文档皆使用此相同数据值而重复生成 "abc123_12345.csv"。

 

*

一个循环占位栏的起始数据值是其在当前页面内使用的第一个值。这是我们真正想要的,所以 [InvoiceNum] 必须保留为一个纯循环内占位栏。

3.

If-Same 內的 [InvoiceNum] 占位栏附有一个列数。由於其他 [InvoiceNum] 均没有使用列数,故此「第二个值」并不会被 Mergemill 视为巳使用的,而谨作为前瞻性比较之用,以查看下一个发票编号有否改变。如果当前发票编号巳是相同的最後一个,便要退出循环,并生成文档。

 


** 作业 1

1.

添加「数据源」>「作业1输出」文件夹作为一个「源文件夹」。

2.

这项作业所生成的文档将被储存到「输出」文件夹,所以你需要指定它为「输出文件夹路径」。

 

*

每个输出档名皆以 "rpt_" 开始,随之是客户编号,如 "rpt_abc123.csv"

 

*

每个输出文档皆包含一个标题行 ("InvoiceNum","AL","AK",...[至包括所有州份]),接着是每张发票一行的相关数据,如 "12345","0",...,"2","0","0","1",...,"1",...

3.

此作业的模板只包含这两行脚本 (蓝色标签只为提高可读性):

 

L1

"InvoiceNum","AL","AK","AZ","AR","CA","CO","CT","DC","DE","FL","GA","HI","ID",
"IL","IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NM","NY",
"NJ","NC","ND","OH","OK","OR","PA","RI","SC","SD","TN","TX","UT","VT","VA","WA","WV","WI","WY"<?Loop?>

 

L2

"<?[InvoiceNum]?>","<?[AL]?>","<?[AK]?>","<?[AZ]?>","<?[AR]?>","<?[CA]?>","<?[CO]?>","<?[CT]?>","<?[DC]?>","<?[DE]?>","<?[FL]?>","<?[GA]?>","<?[HI]?>","<?[ID]?>","<?[IL]?>","<?[IN]?>","<?[IA]?>","<?[KS]?>","<?[KY]?>","<?[LA]?>","<?[ME]?>","<?[MD]?>","<?[MA]?>","<?[MI]?>","<?[MN]?>","<?[MS]?>","<?[MO]?>","<?[MT]?>","<?[NE]?>","<?[NV]?>","<?[NH]?>","<?[NM]?>","<?[NY]?>","<?[NJ]?>","<?[NC]?>","<?[ND]?>","<?[OH]?>","<?[OK]?>","<?[OR]?>","<?[PA]?>","<?[RI]?>","<?[SC]?>","<?[SD]?>","<?[TN]?>","<?[TX]?>","<?[UT]?>","<?[VT]?>","<?[VA]?>","<?[WA]?>","<?[WV]?>","<?[WI]?>","<?[WY]?>"<?If(Same[ClientCode]{2})?><?Else?><?Exit?><?EndIf?><?EndLoop?>

4.

选取「文件夹」为所有占位栏的数据馈送源,然後选用「数据源」>「作业1输出」文件夹。你亦要选用「视数据馈送为单一数据流」设定。你可用 Mergemill Pro 的「全批处理」功能快速设定这 53 个占位栏的相同选项。

5.

设定输出档名为 "rpt_"+[ClientCode]+".csv"。

6.

设定按 [ClientCode] 将数据馈送排序。

spacer

内容插入

算术表达式

循环

分支

系统值

统计函数

区段

范例

spacer

返回页首

软件功能spacer::spacer下载专区spacer::spacer购买spacer::spacer软件支援spacer::spacer视频教程spacer::spacer标签指南spacer::spacer网站导览


版权所有 · 不得转载 © 2001-2017 Cross Culture Ltd.