Generation of Time dependent records with one source-field

[Former Member]

Hi experts

Below is a screen from a master data table in ERP.

Key fields VTREF and SETFLAG.

The data field MDRKD is a date field.

In BW extracted a transformation should generate time dependent records, example for VTREF = 0001:

0001 F   26.01.2005  23.03.2005

0001 P   24.03.2005  10.02.2005

and so forth...

Is there any coding sample for such a requirement?

Thanks

T. 

[sander_vanwilligen · Accepted solution]

Hi Thomas,

I don't have a coding example available but the logic should not be too difficult.

You have to sort the data package on key fields in ascending order and time field in descending order. Then you loop over the data package.

You need variables to record the previously processed key fields in order to detect a new combination and to start the Valid To with 31/12/9999. You also need a variable to record the previously processed Valid From date. The Valid To date for the next time slice of the same combination will be the previously processed Valid From date + 1 day.

Note: if the data load would split up in more than one data package, then you will need Semantic Grouping on the key fields in order to keep the groups in the same data package.

Best regards,

Sander

[Former Member · Accepted solution]

Thanks Sander

I think the valid to date for the next time slice of the same combination will be the previously processed Valid From date - 1 day, as per example:

08.08.2015  - 31.12.9999

12.02.2014  - 07.08.2015

14.02.2013 -  11.02.2014

....

[Former Member · Accepted solution]

Hi all

We have done the sorting in the start routine.

The endroutine also works fine, except when having duplicates in field MDRKD.

You can see the duplicates in the screen above.

We don't need these two lines at all. How can we get rid off these lines? What is the proper

ABAP coding?

Thanks

T.

  IF RESULT_PACKAGE[] IS NOT INITIAL.

      LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>.

        IF WA_TARGET-FC_VTREF IS NOT INITIAL.

          IF WA_TARGET-FC_VTREF = <RESULT_FIELDS>-FC_VTREF
            AND WA_TARGET-DATEFROM < <RESULT_FIELDS>-DATEFROM.
            WA_TARGET-DATETO = <RESULT_FIELDS>-DATEFROM - 1.
          ELSE.
            WA_TARGET-DATETO = '99991231'.
          ENDIF.

          APPEND WA_TARGET TO ITAB_TARGET.
          CLEAR: <result_fields>.

        ENDIF.
        WA_TARGET = <RESULT_FIELDS>.

      ENDLOOP.
      REFRESH RESULT_PACKAGE.
      RESULT_PACKAGE[] = ITAB_TARGET[].
    ENDIF.

[sander_vanwilligen · Accepted solution]

Hi Thomas,

Yes, you are of course right. The Valid To of the next time slice is the previous Valid From date MINUS 1 day.

Best regards,

Sander

[sander_vanwilligen · Accepted solution]

Hi Thomas,

The trick must be to sort the RESULT_PACKAGE on key field(s) in ascending order and date field in descending order. After that delete adjacent duplicates comparing the key field(s). If I understand it correctly, the only key field to take into account is VTREF.

Coding could then look like this:

SORT RESULT_PACKAGE BY VTREF ASCENDING MDRKD DESCENDING.

DELETE ADJACENT DUPLICATES FROM RESULT_PACKAGE COMPARING VTREF.

Best regards,

Sander

[Former Member · Accepted solution]

Hi Sander

We need to delete the duplicates completely.

DELETE ADJACENT is not enough.

Key field is VTREF and SETFLAG.

In the example below you can see one key field VTREF value (covered) and two SETFLAG values, F and P. Data field MDRDD is 11.02.2009.

We don't need these two lines, both have to be deleted.

Therefore DELETE ADJACENT is not the right coding...

Any ideas?

Thanks

T.

[sander_vanwilligen · Accepted solution]

Hi Thomas,

That's not an easy requirement but could the following approach work for you:

1. Sort table RESULT_PACKAGE on VTREF and SETFLAG in ascending order and date field in descending order;
2. Loop over RESULT_PACKAGE;
3. Read table RESULT_PACKAGE with key VTREF, the different SETFLAG than the once selected and MDRKD
4. If found, then delete both records from RESULT_PACKAGE (i.e. with key VTREF and MDRKD);
5. After the loop, delete adjacent duplicates comparing VTREF and SETFLAG;
6. After that, continue with the logic I suggested on Aug 15, 2015 1:40 AM (with the minus 1 day of course).

Best regards,

Sander

[Former Member]

Load it via a time dependent datasource and the system will automatically do this for you give the valid from date as the date mentioned.

BR,

Suyash

[Former Member]

Hi Suyash

I have only one date field, DATETO. DATEFROM has to be generated.

Check the screen above or

how to set datasource time dependant? | SCN

Regards

T