Topic
8 replies Latest Post - ‏2014-06-24T16:14:08Z by Arun Ramamurthy
NASCARFan
NASCARFan
19 Posts
ACCEPTED ANSWER

Pinned topic DTM DateTime mapping to separate XML elements

‏2014-06-23T17:13:38Z |

Hello and Good Day, ALL!

I have run into a bit of a challenge for which I am sure a solution exists.

I am able to map Date & Time from a single DTM segment string to separate XML elements, where:

DTM+189:1309271540:201'

Yields:

<DepartureDate>20130927</DepartureDate>

<DepartureTime>1540</DepartureTime>

 

HOWEVER, the input DT/TM string MAY optionally feature a six byte DATEabsent 'CC' DTM+189:09271540:201'

While at one time I was able to get the below rule to work for 6 byte DATES, its NOW yielding an EMPTY <DepartureDate> element, but <DepartureTime> is accurately mapped.

 

=IF (TEXTTONUMBER( DateTimePeriodFunctCdQual'r Element:DateTimePeriod Composite:DTM Segment:Group3:In1 ) = 189, EITHER( FROMDATETIME(TODATETIME(LEFT(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3:In1  , 12),   "{CCYYMMDDHH24MM}"), "{CCYYMMDD}"),  FROMDATETIME(TODATETIME(LEFT( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3:In1  , 10), "{YYMMDDHH24MM}"), "{CCYYMMDD}")), NONE)

 

THEN, as for TIME:

The <DepartureTime>  rule I am using is: 

=IF (TEXTTONUMBER( DateTimePeriodFunctCdQual'r Element:DateTimePeriod Composite:DTM Segment:Group3:In1 ) = 189,

 RIGHT( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3:In1 , 4), NONE)

 

BUT interestingly, when the TIME is missing (DTM+189:130927:201')   the results are: 

<DepartureDate></DepartureDate>

<DepartureTime>0927</DepartureTime>

 

Personally, I want to tell the Test team that since the data is required by the application, bad and  insufficient data is just that.  However, I want to pursue this if it can be done accurately.

Any feedback most appreciated!

Bud - Customs

 

  • Tim Rabbitt
    Tim Rabbitt
    38 Posts
    ACCEPTED ANSWER

    Re: DTM DateTime mapping to separate XML elements

    ‏2014-06-23T18:34:56Z  in response to NASCARFan

    Hello:

    Based on what you've described, your going to have one of the following scenarios as input:

    1) CCYYMMDDHHMM (12 bytes)

    2) YYMMDDHHMM (10 bytes)

    3) CCYYMMDD (8 bytes)

    4) YYMMDD (6 bytes)

    You could modify the rule to use an if clause based on length of DateTimePeriodVal with the SIZE function checking for sizes of 12, 10, 8 and 6 bytes and then format it with TODATETIME

     

    Regards,

    Tim

     

    • NASCARFan
      NASCARFan
      19 Posts
      ACCEPTED ANSWER

      Re: DTM DateTime mapping to separate XML elements

      ‏2014-06-23T19:52:11Z  in response to Tim Rabbitt

      Thank you for the feedback, Tim.

      I put together the below rule. While it complies 'clean', the result is an empty XML element.

      Thoughts?

      =IF(SIZE( DateTimePeriodFunctCdQual'r Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1) = 12 FROMDATETIME(TODATETIME(LEFT(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3:In1  , 12),   "{CCYYMMDDHH24MM}"), "{CCYYMMDD}"), 

      IF(SIZE( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment[1]:In1) = 10 FROMDATETIME(TODATETIME(LEFT( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1, 10),   "{YYMMDDHH24MM}"), "{YYMMDDHHMM}"),

       IF(SIZE(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment[1]:In1) = 8 FROMDATETIME(TODATETIME(LEFT(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1  , 8),   "{CCYYMMDD}"), "{CCYYMMDD}"),

       IF(SIZE( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment[1]:In1) = 6 FROMDATETIME(TODATETIME(LEFT( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1, 6),   "{YYMMDD}"), "{YYMMDD}"),

       NONE))))

      • Tim Rabbitt
        Tim Rabbitt
        38 Posts
        ACCEPTED ANSWER

        Re: DTM DateTime mapping to separate XML elements

        ‏2014-06-23T20:37:36Z  in response to NASCARFan

        Hello:

        Using blob input and output, this rule works for me:

        =IF (SIZE(in1)=12,  datetotext( todatetime(in1,"CCYYMMDDHH24MM")),
                   IF (SIZE(in1)=10,    datetotext( todatetime(in1,"YYMMDDHH24MM")),
                         IF (SIZE(in1)=8,   datetotext(todatetime(in1,"CCYYMMDD")),
                             IF (SIZE(in1)=6, datetotext(todatetime(in1,"YYMMDD")), "invalid date"))))

        Regards,

        Tim

        • NASCARFan
          NASCARFan
          19 Posts
          ACCEPTED ANSWER

          Re: DTM DateTime mapping to separate XML elements

          ‏2014-06-23T21:15:55Z  in response to Tim Rabbitt

          <sigh>   Similar results; "Invalid Date" this time (obviously).  Something else in play here.

          Target <DepartureDate> is maxlen 12.   Input is  DTM+189:201309271111:201'

           

          =IF (SIZE( DateTimePeriodFunctCdQual'r Element:DateTimePeriod Composite[1]:DTM Segment:Group3:In1 ) = 12, DATETOTEXT( TODATETIME(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1, "CCYYMMDDHH24MM")),

           IF (SIZE(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment[1]:In1 ) = 10, DATETOTEXT( TODATETIME( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1 ,"YYMMDDHH24MM")),

           IF (SIZE(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment[1]:In1 ) = 08, DATETOTEXT( TODATETIME(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1 ,"CCYYMMDD")),

           IF (SIZE( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment[1]:In1 ) = 06, DATETOTEXT( TODATETIME( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1 ,"YYMMDD")),

           "invalid date"))))

           

          • NASCARFan
            NASCARFan
            19 Posts
            ACCEPTED ANSWER

            Re: DTM DateTime mapping to separate XML elements

            ‏2014-06-24T14:59:10Z  in response to NASCARFan

            Got it working.  THANK YOU MUCH for the assist!

            • Arun Ramamurthy
              Arun Ramamurthy
              53 Posts
              ACCEPTED ANSWER

              Re: DTM DateTime mapping to separate XML elements

              ‏2014-06-24T15:18:41Z  in response to NASCARFan

              Looks like you had the incorrect field in the first IF comparison..

              =IF (SIZE( DateTimePeriodFunctCdQual'r Element:DateTimePeriod Composite[1]:DTM Segment:Group3:In1 ) = 12, DATETOTEXT(

              You should have had - DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:In1,

              looks like you changed it and got it working.

               

               

               

              • NASCARFan
                NASCARFan
                19 Posts
                ACCEPTED ANSWER

                Re: DTM DateTime mapping to separate XML elements

                ‏2014-06-24T16:07:08Z  in response to Arun Ramamurthy

                Actually, Arun, the DTM in SG 3 is the correct In1 reference. (The source for this mapping is an EDIFACT PAXLST.) 

                Here is the mapping that worked for Departure Date &  Time. 'CC' maintained or derived.   The Arrival Date & Time uses same with Occurence [2]:

                Date Mapping

                =IF(SIZE( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1) = 12 FROMDATETIME(TODATETIME(LEFT( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1  , 12),   "{CCYYMMDDHH24MM}"), "{CCYYMMDD}"),

                 IF(SIZE( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1) = 10 FROMDATETIME(TODATETIME(LEFT DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1, 10),   "{YYMMDDHH24MM}"), "{CCYYMMDD}"),

                 IF(SIZE(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1  ) = 8 FROMDATETIME(TODATETIME(LEFT DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1  , 8),   "{CCYYMMDD}"), "{CCYYMMDD}"),

                 IF(SIZE( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1  ) = 6 FROMDATETIME(TODATETIME(LEFT DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1  , 6),   "{YYMMDD}"), "{CCYYMMDD}"),

                 "invalid date"))))

                 

                Time Mapping

                =IF(SIZE(DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1  ) = 12,

                 RIGHT( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1, 4),

                  IF(SIZE DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1) = 10,

                 RIGHT( DateTimePeriodVal Element:DateTimePeriod Composite:DTM Segment:Group3[1]:In1, 4),

                   NONE))

                • Arun Ramamurthy
                  Arun Ramamurthy
                  53 Posts
                  ACCEPTED ANSWER

                  Re: DTM DateTime mapping to separate XML elements

                  ‏2014-06-24T16:14:08Z  in response to NASCARFan

                  Bud,

                  Glad you got it working. I meant to say you had DateTimePeriodFunctCdQual'r  earlier in the comparison (comparing its size to 12) which was probably why it was getting to empty value or "Invalid date" later.  I see you are now using DateTimePeriodVal in that part.

                  Thanks,

                   

  • This reply was deleted by NASCARFan 2014-06-23T21:13:36Z.