Gareth Horton
July 12th, 2002, 12:46 PM
Hey finally found a use for the second solution below.
We had a download of all our Vendors in December and in January we purged all vendors no longer in use.
We did a new download and then used the comparison solution you used to see where which vendors were deleted.
This solution was much quicker than downloading both reports into XL and doing the comparison.
This really helped a lot!
Michael Jul Hansen wrote:
> Problem: In a very large report containing shipping documents I needed to pick out the shipments that were transshipped (i.e.
taken off one vessel and loaded to another vessel). The documents I needed all had a so called ‘transhipment clause’ in the description of goods in the document. This clause has a very specific format: ‘cargo transshipped ex the m.v. XXXXX in YYYYY’ where XXXXX is the vessel the cargo came off and YYYYY is the port in which it happened.
> Two typical examples of such a clause could be:
> ‘cargo transshipped ex the m.v. Anna Maersk voyage 9901 in Singapore’ and
> ‘cargo transshipped ex the m.v. Margrethe Maersk voyage 9908 in Kobe’
> (Our vessel table has 17 chars for the name of the vessel and the port table 20 for port names)
>
> Solution
>
> Granted the fact that the clause can appear anywhere in the description column and that this column is of highly variable length, the solution was the following:
>
> 1) Make a template that captures the description as a variable length memo field, DESCRIPTION.
> 2) Make a calculated numeric field ‘TEST’ with the following syntax: Instr("ex the m.v.",description) – this field will return the starting position of the string "ex the m.v.".
> 3) Knowing that the vessel name starts exactly 13 chars after the position of TEST, we can now make three new calculated fields, EX_VESSEL, EX_VOYAGE, and TRANSHIPMENT_PORT using the SUBSTR function like this:
> EX_VESSEL: Substr(description,(test+13),17)
> EX_VOYAGE: Substr(description,(test+38),4)
> TRANSHIPMENT_PORT: Substr(description,(test+46),20)
>
> Now, we were lucky enough that the report did not remove blanks after the vessel name, but if it had, we could have determined the length of the actual vessel name in the document by making another calculated field TEST2 using the instr function that looked for the position of the word ‘voyage’ and then our ‘length’ parameter in the substr function would have been expressed as ‘(TEST2-TEST+13)’
>
> Simple???? – Maybe not, but it WORKS!!!
>
> Lets take another one:
>
> Problem: An import customer service department in a shipping company prints a report that gives Arrival Notices which are mailed to the importers. This is done 10 days prior to the vessels arrival in the port. – The vessel is coming in to Europe from the Far East, and the majority of the shipping documents are registered in the system those 10 days prior to arrival. However, the vessel loads cargo destined for our port enroute, in e.g. the Middle East and in other European ports. Previously,
a simple report was pulled together with the arrival notices, listing only the shipping document numbers, and then two days prior to arrival, the same report was pulled and these two reports were checked manually to find documents that had been added since the first report was pulled, so that it could be determined which customers had not received an arrival notification. The experienced Monarch user instantly thinks – here my favorite software must be able to assist!
>
> Solution
>
> Here is how it’s done:
>
> As today, pull the simple report listing shipping document numbers 10 days prior to arrival – but this time don’t print it, download it and call it say ‘reconcile.prn’.
> 1) Pull the report again two days prior to arrival, download it and call it ‘reconcile1.prn’.
> 2) Now, make a Model that in all its simplicity has ONE field, SHIPPING_DOC_NO. Make a calculated field, REPORT, using the function file().
> 3) Make a Summary with key field REPORT displayed across, matching all, and key field SHIPPING_DOC_NO displayed down, matching summ field and click radio button is at most 1.
> The result is a list of shipping documents to the left and the two report names across – if it says 0 under report reconcile.prn and 1 under report reconcile1.prn, this naturally means that the document was added in the days between the two reports, and if it says 1 under reconcile.prn and 0 under reconcile1.prn, then the document was deleted in the interim period.
>
> This method can be used for all sorts of reports where data in them is dynamic and you want to see the difference, for example a customer table where you wish to see which customers were added since you last pulled the report – here you would do the same, only this time your model should probably capture the field CUSTOMER_NO. There are ENDLESS possibilities – in my opinion only your imagination sets the limit!!!!!
>
>
>
>
>
We had a download of all our Vendors in December and in January we purged all vendors no longer in use.
We did a new download and then used the comparison solution you used to see where which vendors were deleted.
This solution was much quicker than downloading both reports into XL and doing the comparison.
This really helped a lot!
Michael Jul Hansen wrote:
> Problem: In a very large report containing shipping documents I needed to pick out the shipments that were transshipped (i.e.
taken off one vessel and loaded to another vessel). The documents I needed all had a so called ‘transhipment clause’ in the description of goods in the document. This clause has a very specific format: ‘cargo transshipped ex the m.v. XXXXX in YYYYY’ where XXXXX is the vessel the cargo came off and YYYYY is the port in which it happened.
> Two typical examples of such a clause could be:
> ‘cargo transshipped ex the m.v. Anna Maersk voyage 9901 in Singapore’ and
> ‘cargo transshipped ex the m.v. Margrethe Maersk voyage 9908 in Kobe’
> (Our vessel table has 17 chars for the name of the vessel and the port table 20 for port names)
>
> Solution
>
> Granted the fact that the clause can appear anywhere in the description column and that this column is of highly variable length, the solution was the following:
>
> 1) Make a template that captures the description as a variable length memo field, DESCRIPTION.
> 2) Make a calculated numeric field ‘TEST’ with the following syntax: Instr("ex the m.v.",description) – this field will return the starting position of the string "ex the m.v.".
> 3) Knowing that the vessel name starts exactly 13 chars after the position of TEST, we can now make three new calculated fields, EX_VESSEL, EX_VOYAGE, and TRANSHIPMENT_PORT using the SUBSTR function like this:
> EX_VESSEL: Substr(description,(test+13),17)
> EX_VOYAGE: Substr(description,(test+38),4)
> TRANSHIPMENT_PORT: Substr(description,(test+46),20)
>
> Now, we were lucky enough that the report did not remove blanks after the vessel name, but if it had, we could have determined the length of the actual vessel name in the document by making another calculated field TEST2 using the instr function that looked for the position of the word ‘voyage’ and then our ‘length’ parameter in the substr function would have been expressed as ‘(TEST2-TEST+13)’
>
> Simple???? – Maybe not, but it WORKS!!!
>
> Lets take another one:
>
> Problem: An import customer service department in a shipping company prints a report that gives Arrival Notices which are mailed to the importers. This is done 10 days prior to the vessels arrival in the port. – The vessel is coming in to Europe from the Far East, and the majority of the shipping documents are registered in the system those 10 days prior to arrival. However, the vessel loads cargo destined for our port enroute, in e.g. the Middle East and in other European ports. Previously,
a simple report was pulled together with the arrival notices, listing only the shipping document numbers, and then two days prior to arrival, the same report was pulled and these two reports were checked manually to find documents that had been added since the first report was pulled, so that it could be determined which customers had not received an arrival notification. The experienced Monarch user instantly thinks – here my favorite software must be able to assist!
>
> Solution
>
> Here is how it’s done:
>
> As today, pull the simple report listing shipping document numbers 10 days prior to arrival – but this time don’t print it, download it and call it say ‘reconcile.prn’.
> 1) Pull the report again two days prior to arrival, download it and call it ‘reconcile1.prn’.
> 2) Now, make a Model that in all its simplicity has ONE field, SHIPPING_DOC_NO. Make a calculated field, REPORT, using the function file().
> 3) Make a Summary with key field REPORT displayed across, matching all, and key field SHIPPING_DOC_NO displayed down, matching summ field and click radio button is at most 1.
> The result is a list of shipping documents to the left and the two report names across – if it says 0 under report reconcile.prn and 1 under report reconcile1.prn, this naturally means that the document was added in the days between the two reports, and if it says 1 under reconcile.prn and 0 under reconcile1.prn, then the document was deleted in the interim period.
>
> This method can be used for all sorts of reports where data in them is dynamic and you want to see the difference, for example a customer table where you wish to see which customers were added since you last pulled the report – here you would do the same, only this time your model should probably capture the field CUSTOMER_NO. There are ENDLESS possibilities – in my opinion only your imagination sets the limit!!!!!
>
>
>
>
>