主頁spacer>spacerMergemill 標籤指南spacer>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 視為巳使用的,而謹作為前瞻性比較之用,以查看下一個發票編號有否改變。如果當前發票編號巳是相同的最後一個,便要退出循環,並生成文檔。

 


** 作業 2

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.