How to "merge" different records in BPC 10.1 (fox formula)

[former_member717250]

Hi everyone,

I know there are similar questions but no one could help me. Hope you can.

I have in the same ADSO two set of data which I need to combine, I have tried several fox formula but without result.

Let's say I have period 0calmonth and 0calyear, 0compcode, z_activity, z_location,z_structure,z_proyect, kf1, kf2, kf3

Set A:

202201,2022,,ACT001,,,,100,,50

202201,2022,,ACT002,,,,100,,50

202201,2022,,ACT003,,,,100,,50

202201,2022,,ACT004,,,,100,,50

So, in Set A I only have time and activity and kf1 and kf3

Set B:

202201,2022,0100,,City1,STR1,PR1,,,3

202201,2022,0100,,City2,STR1,PR1,,,5

In Set B I only have time, comp code, location, structure and proyect an kf3

So I need to create n records of set B for each record of Set A,completing every field. For example for ACT001 I need to create this:

202201,2022,0100,ACT001,City1,STR1,PR1,(100*3),,50

202201,2022,0100,ACT001,City2,STR1,PR1,(100*3),,50

For ACT001 I need to create a record with city1 and for city2

I have tried several fox coding but I can't merge the data. I hope you could help me with this issue

An example of code I have tried

{kf1,0calmonth,0calyear, 0compcode, z_activity, z_location,z_structure,z_proyect}=

{kf10calmonth,0calyear,#, z_activity,#,#,#, kf1}*{kf3,0calmonth,0calyear, 0compcode,#, z_location,z_structure,z_proyect}

inside a for each with all or individually but without success, also I have tried a repost (or combining or merge in spanish) from the standard functions.

The standard function keep processing so I force to end the transaction and the fox formula don't get me the merge record

Hope you can help me

Thanks

[gregor_dieckmann]

Hi Diego,

the screen shot shows the after image.

You have not mentioned what you want to do with the old records, i.e. with the sets A and B. Observe, that in FOX you never overwrite 'key fields', i.e. characteristics to be changed. In FOX you always create new records, set key figure values in a record or delete records (key figure values are 0).

I assume you have sets A and B in the preimage (maybe even other records not in A and not in B) and it seems that you want to create a new set C that contains the 'merged' records. Your code may create new records in C but it does not delete old records, i.e. the records in A and B.

Regards,

Gregor

[former_member717250]

Hi Gregor

I want to jeep the old récords, because they are the base and the user could change them. But my new récords are still separated 😞

[gregor_dieckmann]

Hi Diego,

you still use two nested loops without checks on time, so you get the cart. product. I think you only need one internal table to 'lookup' the records of set A; this is the 'where' condition I mentioned, but this does not exist in internal tables. So you have to use a corresponding IF statement in the inner loop: pseudo code

loop at set B

check whether records exists in A ( using EXISTS ...) based on time

no: maybe set some defaults or do nothing, depending on your requirements

yes: now you know that records exist in A for the current time value B.time

loop at internal table A

if current.time = B.time

implement the logic ...

endif

You might also optimize the inner loop as the records are sorted by time values; so for the time values current.time <> B.time you have at most two blocks: the time valuey < B.time and the values > B.time. So you might exit the inner loop when you reach the second block. As there seems to be no comparison operation on characteristic values you may use an integer based flag, set it 1 before the loop, set it to 1 when current.time = B.time and exit the loop if the flag is 0 and current.time <> B.time. The latter is only an optimization but might be helpful to improve performance.

Regards,

Gregor

[former_member717250]

HI gregor.dieckmann

thanks for the pseudo-code

I follow it, but still I can't get the desired result. Sorry I need a bit more help from you.

this is my code, I put in comments your instructions. And in the attached image the current result.

*--LOOP AT SET B
FOREACH V_TIEMPO,V_ZONA.
VTIM=V_TIEMPO.
*--check whether records exists in A ( using EXISTS ...) based on time
CNT = EXISTS( INT_TAB.{V_TIEMPO,V_ACTIV} ).
IF NOT CNT=1.
EXIT.
ELSE.
*--loop at internal table A
FOREACH V_TIEMPO,V_ACTIV IN INT_TAB.
*-----if current.time = B.time
IF VTIM=V_TIEMPO.
{Z_QCANTVR,V_TIEMPO,V_ACTIV,V_ZONA,MOE}=1.
ENDIF.
ENDFOR.
ENDIF.
ENDFOR.

Please help, what am I missing.

Thanks in advance (very much)

[gregor_dieckmann]

Hi Diego,

so it seems that you want to loop at set B and then loop at set A where B.time = A.time. You can put set A in an internal table and then loop as indicated above over A and over the internal table based on equal time field. In the FOX editor use the help button and read the documentation about internal tables.

Regards,

Gregor

[former_member717250]

Hi Gregor

Thanks for your answer.

I tried your solution which I think to be the best solution, but something is wrong with my code. I'm still getting activities and cities separetely.

DATA V_ZONA TYPE Z_CD_ZONA.
DATA V_ACTIV TYPE Z_ACTIVI.
DATA V_TIEMPO TYPE 0CALMONTH.

TABLE INT_TAB {TI_TIEMPO TYPE 0CALMONTH KEY, TI_ACT TYPE Z_ACTIVI KEY, ZVAL TYPE F}.
TABLE INT_TAB2 {TI_TIEMPO TYPE 0CALMONTH KEY, TI_ZON TYPE Z_CD_ZONA KEY, ZVAL2 TYPE F}.

FOREACH V_TIEMPO,V_ACTIV.
VRES={Z_QCANTID,V_TIEMPO,V_ACTIV,#}.
MESSAGE I000(/Z_BPC_IZZI/)WITH V_TIEMPO V_ACTIV VRES.
INT_TAB.{ZVAL,V_TIEMPO,V_ACTIV} = VRES.
ENDFOR.

FOREACH V_TIEMPO,V_ZONA.
VRES1={Z_CANT,V_TIEMPO,#,V_ZONA}.
MESSAGE I001(/Z_BPC_IZZI/)WITH V_TIEMPO V_ZONA VRES1.
INT_TAB2.{ZVAL2,V_TIEMPO,V_ZONA} = VRES1.
ENDFOR.
*-------------------------
FOREACH V_TIEMPO,V_ACTIV IN INT_TAB.
FOREACH V_TIEMPO,V_ZONA IN INT_TAB2.
VRES2=INT_TAB.{ZVAL,V_TIEMPO,V_ACTIV}*INT_TAB2.{ZVAL2,V_TIEMPO,V_ZONA}.
MESSAGE I002(/Z_BPC_IZZI/)WITH VRES2.
ENDFOR.
ENDFOR.

Hope you can help me, and thanks