Homespacer>spacerMergemill Tags Guidespacer>spacerAutomated Data Processing

spacer

Content Field

Expression

Looping

Branching

System Values

Statistics

Sections

Example

spacer

This example, though seems long due to the many states in the US, illustrates the use of field, expression, loop, branching structure, and variable tags with easy-to-understand template script logic to quickly solve a problem that may take a lot more effort and time by other means.


The Problem

For taxation reasons, a US direct marketing company needs to provide each client with a distribution report by state for each mailing they sent out. The raw data they have for each client are contained in a single file named using the client code, such as "abc123.csv". There are two columns of data in each of the files, like

 

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

For each client, they need to deliver a file with content like this in the end:

 

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

The name of each completed client report begins with "rpt_", and is followed by the client code, like "rpt_abc123.csv".

space
The Solution

We need to set up two jobs for the project:

*

Job 1 tallies the mailings for each state for each invoice

*

Job 2 takes the results from Job 1 and puts all the rows into one tabular output

 


** JOB 1

1.

Set up a "Job" folder containing these subfolders:

 

*

"Source" folder contains all the client source files

 

*

"Output" is for the completed reports

 

*

"Template" is for the job templates

2.

Inside the "Source" folder, add the subfolders "Job1Output".

3.

Files generated by this job will be saved to the folder "Source" > "Job1Output". So you need to specify this as the "Output Folder Path".

 

*

Each output file is named by the client code followed by the invoice number separated by an underscore, like "abc123_12345.csv"

 

*

Each of these file contains one header row ("ClientCode","InvoiceNum","AL","AK",...[so on for all the states]) followed by just one data row for the invoice like "abc123","12345","0",...,"2","0","0","1",...,"1",...

4.

The template for this job contains these TWO lines (some tags are highlighted in blue solely to improve readability):

 

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.

Data feed for fields:

 

*

Use "Autotext" as Data Feed Source for [ClientCode] and specify "Source filename" only in the Autotext definition

 

*

Use "Folder" as Data Feed Source for both [InvoiceNum] and [State], and specify "Source" as the folder. Of course, you need to add "Source" as a source folder first

6.

Specify the output filename to be "Source filename"+"_"+[InvoiceNum]+".csv".

7.

Specify to sort the data feed by [InvoiceNum].

 

JOB 1 Template Explained

1.

The template instructs Mergemill Pro to first create a header row of field names (L1), then initialize the variables for the tallies, loop to tally the mailings, and add the row of formatted final counts on the completion of the loop for the current invoice number. Since this is the end of the template, the file is generated.

2.

L2 begins with an out-loop [State] field hidden in a Hide section. This needs to be done because

 

*

We use the source filename both as values for the [ClientCode] field and in building the output file names.

 

*

Mergemill always uses the FIRST out-loop field in the template to determine the source filename for the current page.

 

*

We cannot use [InvoiceNum] for this out-loop field because we also use its data value in building the output file names, and Mergemill always uses the starting data value for the field as a filename component.

 

*

For an out-loop field, the starting data value is the first one in the current stream, which in this case is the first value in the source file. So for the source file "abc123.csv", this value for [InvoiceNum] is always "12345". All generated files for the invoices will use the same name, and overwrite the first file "abc123_12345.csv".

 

*

For an in-loop field, the starting data value is the first value of the field for the current page. This is what we really want, and so [InvoiceNum] has to be in-loop only.

3.

A column number is added to the If-Same field [InvoiceNum]. Since the column number is not added elsewhere, that "next" value is not considered used by Mergemill, but is used only for a "look-ahead" comparison to determine if there is a change in the next value of invoice number. If the current invoice number is the last of the same, then the loop is exited and the file is generated.

 


** JOB 2

1.

Add "Source" > "Job1Output" as a source folder.

2.

Files generated by this job will be saved to the folder "Output". So you need to specify this as the "Output Folder Path".

 

*

The name of each output file begins with "rpt_", and is followed by the client code, like "rpt_abc123.csv"

 

*

Each of these file contains one header row ("InvoiceNum","AL","AK",...[so on for all the states]) followed by one data row for each invoice like "12345","0",...,"2","0","0","1",...,"1",...

3.

The template for this job contains these TWO lines (some tags are highlighted in blue solely to improve readability):

 

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.

Use "Folder" as Data Feed Source for all fields, and specify "Source" > "Job1Output" as the folder. Check the option "Data feed as a single stream". You may use the batch feature in Mergemill Pro to specify settings for all the 53 fields easily.

5.

Specify the output filename to be "rpt_"+[ClientCode]+".csv".

6.

Specify to sort the data feed by [ClientCode].

spacer

Content Field

Expression

Looping

Branching

System Values

Statistics

Sections

Example

spacer

Top of Page

Featuresspacer::spacerDownloadsspacer::spacerBuy Nowspacer::spacerSupportspacer::spacerTutorialsspacer::spacerTags Guidespacer::spacerSite Map


Copyright © 2001-2017 Cross Culture Ltd. All Rights Reserved.