cancel
Showing results for 
Search instead for 
Did you mean: 

Fox formula FOREACH loop for multiprovider

Former Member
0 Kudos

Hi,

I have a multiprovider with two cubes Cube A and Cube B. I have aggregation level on this multiprovider and fox formula.

Cube A is having 100 records and Cube B is having 5 records.

I want to loop on this Cube B and for that 5 records need to findout the matching records from Cube A and need to copy the missing field data from A to B. Like in the below example I need to match Cube A and B with Field 1 and need to copy field 2 and field3 data to cube B.

But my FOREACH loop is reading all the records from CUBE A and CUBE B. It is repeating 105 times ( as in cube A i have 100 recs and CUBE B have 5 recs). but I wan to use the loop only on CUBE B and it has work only for 5 times.

Ex:

Cube A :

Field1 , Field 2, Field 3, Field 4

AA BB CC DD

Cube B:

Field1 Field2 Field3 Field4

AA # # DD

finally cube B sould be like this: AA BB CC DD.

Can anybody through some light on this or any sample code.

Thanks,

V.Senthil

Edited by: Senthilkumar Viswanathan on Feb 11, 2012 11:02 AM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

If you are interested on only to loop 5 times instead of 105,then you should filter your function only on cube B and in your FOX you should use FOREACH....ENDFOR and not FOREACH IN REFDATA....ENDFOR.

Regards,

Eitan.

Former Member
0 Kudos

Hi Eitan,

Ok. understood that if I restrict on cube B it will only loop on 5 records and it will not loop on cube A records ( 100 records). but I have a doubt here,

As you see in my post I mentioned that for the cube B records I need to findout matching records in Cube A and copy the data to cube B. so in that case if I restrict on Cube B within loop can I access Cube A data?? Is it possible still?.

Ex:

Filter = Cube B

and infoprovider also included in fields to be changed in formula.

in fox formula,

FOREACH field1.

{kf1,field1,field2,field3,cubeB} = {kf1,field1,field2,field3,CubeA}.

ENDFOR.

Will the above example work if I restrict on infoprovider = CUBE B?.

Thanks,

V.Senthil

Edited by: Senthilkumar Viswanathan on Feb 14, 2012 11:26 PM

Edited by: Senthilkumar Viswanathan on Feb 14, 2012 11:29 PM

Former Member
0 Kudos

Hi,

It will be indeed possible.

This is why there is the implicit call for refdata so that you will not have to read and lock it asa part of the filter . reference data is read in FOX for all the data that is requested on the right side of the statement {data-cube_b} = {refdata-cub_a}

Regards,

Eitan.

Former Member
0 Kudos

Hi Eitan,

I tried the same in Fox. But it is not working, I am not getting any data copied to Cube B from Cube A. ( We have the matching records in both Cube A and B).

So I debugged the fox formula and found the loop is iterating again 100 times. Dont know what I am missing..

Thanks,

V.Senthil

Former Member
0 Kudos

Hi,

Please send your FOX code and your filter restrictions,and make sure that your filter restrictions are only retriving 6 records ,as if you created a bex query with that filter and got 6 records upon execution.

Eitan.

Former Member
0 Kudos

Hi Eitan

I am sending you the exact details below,

The two cube names are as follows, GGLC00024 (ACTUALS CUBE) GGLC00016(REALTIMECUBE). 14 and 16 cubes are having same structure.

Multiprovider on this two cubes: GGLC00007

Aggregation level on this Multiprovider including all the chars and kfs of the cubes.

Filter created on this aggregation level on character INFOPROVIDER restricted to cube GGLC00016. ( 16 cube is having only 5 records, it is checked in query and data display from infoprovider).

Now, fox formula is created with the following parameters.

Fields to be changed: AC_DOC_NO, Infoprovider, Costcenter, Business center.

No condition applied.

Fox code:

DATA BUS TYPE 0BUS_AREA . DATA DOCNO TYPE 0AC_DOC_NO. DATA COSTC TYPE 0COSTCENTER.

DATA INFOP TYPE 0INFOPROV. DATA INFOP1 TYPE 0INFOPROV.

INFOP1 = 'GGLC00014'.

INFOP = 'GGLC00016'.

BREAK-POINT.

FOREACH DOCNO.

{0DEB_CRE_LC, DOCNO, BUS,COSTC,INFOP} = {0DEB_CRE_LC, DOCNO, BUS,COSTC, INFOP1}.

ENDFOR.

For the 5 records of cube 16 I am copying business cetner and cost center from 14 cube by matching document no in both the cubes.

Hope I have given all the details.

Thanks,

V.Senthil

Former Member
0 Kudos

Hi ,

1. When you verified that there are only 5 records in display data from cube ,you included the exact characterisitcs as apears in aggregation level?

2. Does you function need to derive characteristics values (business cetner and cost center )? if so, then there are misiing staetements in the FOX like foreach cost center ,businees center in ref data ....

Regards,

Eitan.

Former Member
0 Kudos

Hi Eitan,

1. Yes I have included exact characteritics name as appear in the aggregaion level for the fields to be changed.

but for Infoprovider I have to give cosntant values only rite as mentioned in my fox code?.

2. Yes my function need to derive cost cener and business center wherever I have *matches there for Docno* between the two cubes.

Tell me, If I use FOREACH costcenter buscenter in refdata in fox, will the loop not iterate 100 times because of ref data?.

To avoid this 100 times loop only I am asking to loop on realtime cube (16) as that is having only 5 records and I need to derive costcenter and bus center only for those 5 records..

Thanks,

V.Senthil

Edited by: Senthilkumar Viswanathan on Feb 15, 2012 5:27 PM

balajee_sivakumar
Contributor
0 Kudos

Hi Senthil,

You can try the your same FOX code but with out any filter on info provider and before your FOREACH statement have a 'IF' statement where in you check whether cost center and businees center are '#' or not.

Then you put your same below code.

DATA BUS TYPE 0BUS_AREA . DATA DOCNO TYPE 0AC_DOC_NO. DATA COSTC TYPE 0COSTCENTER.

DATA INFOP TYPE 0INFOPROV. DATA INFOP1 TYPE 0INFOPROV.

INFOP1 = 'GGLC00014'.

INFOP = 'GGLC00016'.

BREAK-POINT.

IF COSTC = '#' or IF BUS = '#'.

FOREACH DOCNO.

{0DEB_CRE_LC, DOCNO, BUS,COSTC,INFOP} = {0DEB_CRE_LC, DOCNO, BUS,COSTC, INFOP1}.

ENDFOR.

ENDIF.

Hope this solves your issue.

Regards,

Balajee

Former Member
0 Kudos

Hi Balajee,

Thanks for the reply. Will try and check your suggesition in the fox code.

But have a doubt here, formula will start to fetch the record at FOREACH loop only rite?. If I place IF condition before FOREACH loop, what data this COSTCENTER and BUSINESS CENTER will be having, it will be blank. So control may not go to loop at all I guess. Please let me know if my assumption is wrong.

And even If some records are matching if IF condition, once the control reaches FOREACH loop, again it will start to read all the data from both the cubes?.

Thanks,

V.Senthil

Answers (1)

Answers (1)

Former Member
0 Kudos

Nobody faced such functionality??