IF and EXTRACT question

[dgerdner]

I have input data like this:

ST*315*4299~
B4***VD~
N9*BM*DE435433~
N9*BN*USQMV411664~
Q2*LIB*U.S*******EB614***L*LIBERTY~
R4*D*K*USBAL*BALTIMORE,D~
DTM*139*20060420*0500~
R4*L*D*DEBRH*BREMERHAVEN~
DTM*140*20060403*1230~
SE*10*4299~

In one of my output fields I want the 3rd element from one of the DTM lines. The logis is:

If B4-03 = 'VD'
use the DTM-02&03 for the R4 loop where R4-01 is 'L'
Else
use the DTM-02&03 for the R4 loop where R4-01 is 'D'
(for purposes of this, I'll use "ABC" instead.

I assumed the code would be:

IF(Ship'tStatusCd Element:B4 Segment:In1="VD", FromDateTime(EXTRACT(Date ElementTM Segment:LoopR4 QO315:In1, PortFunctionCd Element:R4 Segment:LoopR4 QO315:In1="L"),"CCYYMMDD") + " " + EXTRACT(Time ElementTM Segment:LoopR4 QO315:In1, PortFunctionCd Element:R4 Segment:LoopR4 QO315:In1="L"),
"ABC")

Now, if I grab just the Date element it works. If I grab just the time element, it works. But when I put them together (as above) it comes back with a blank.

Any idea?

 

[eyetry]

How is the element/field/column you are mapping to defined?

 

[janhes]

You haven't specified the format of the Time part.

 

[dgerdner]

>>How is the element/field/column you are mapping to defined?<<

Text. I wanted to make it datetime, but I had *a lot* of trouble with that. So to keep it simple I'm just going to make it text.

>>You haven't specified the format of the Time part.<<
The Time element is actually defined as Text, so it's a straight conversion. If I don't concantenat the date the time comes across fine. Likewise, if I don't concantonate the time the date comes across fine.


It appears to me that the problem has to do with two Extract's in a single expression. It's as if Mercator can only do the extract one time per expression, and trying to do it again confuses it.

 

[janhes]

I think you should use

FromDateTime(EXTRACT(Date ElementTM Segment:LoopR4 QO315:In1, PortFunctionCd Element:R4 Segment:LoopR4 QO315:In1="L"),"{CCYYMMDD}") + " " + fromdatetime(EXTRACT(Time ElementTM Segment:LoopR4 QO315:In1, PortFunctionCd Element:R4 Segment:LoopR4 QO315:In1="L"),"{HHMM}")

or you could use datetotext and timetotext.

 

[SeriesConsumer]

EXTRACT is a "Series Consuming Function", i.e. you can use it only once in a rule for the same series (in your case: DTM Segment:LoopR4 QO315:In1). To circumvent the problem you can replace the rule by a functional map, pass the series twice (i.e. two different input cards) and put the logic in the output card of the functional map, there doing EXTRACT on different series. And yes, that's not a but, it's a feature ;-)