Getting the Latest status with respect to DATE entered

[Loed]

Hi guyz, kindly help me in this scenario:

I have a DSO and a CUBE....

DSO:

cust

status code

status date

RAW DATA

cust               status code               status date

payer1                    1                      12/31/2013

payer1                    3                       1/3/2014

payer2                    4                       12/15/2013

payer2                    2                        1/10/2014

CUBE:

cust

0calday

qty (KF)

RAW DATA

cust                    0calday                    qty

payer1                1/1/2014                    10

payer1                1/2/2014                    20

payer1                1/3/2014                    30

payer1                1/4/2014                    40

payer1                1/5/2014                    50

payer2                1/1/2014                    60

payer2                1/2/2014                    70

payer2                1/3/2014                    80

payer2                1/4/2014                    90

payer2                1/5/2014                    100

I need to have this report: (for example I entered 1/1/2014 - 1/5/2014)

cust               status code               0calday              qty

payer1                   1                       1/1/2014              10

payer1                   1                       1/2/2014              20

payer1                   3                       1/3/2014              30

payer1                   3                       1/4/2014              40

payer1                   3                       1/5/2014              50

payer2                   4                       1/1/2014              60

payer2                   4                       1/2/2014              70

payer2                   4                       1/3/2014              80

payer2                   4                       1/4/2014              90

payer2                   4                       1/5/2014              100

Is this possible in query? Or do I need to do this in start routine?

By the way, I can already show the QTY figures, I used CONSTANT SELECTION..My problem now is how to the STATUS CODE..How will I do this?

Thank you!

Loed

[former_member182470 · Accepted solution]

My suggestion is to handle this in modeling level. Enhance your Cube with "Status Code".

Let your Cube data from whatever source you have mapped to. Create a self loop to Cube and write an End routine by looking up from DSO. Hope you get my idea..

[Loed · Accepted solution]

so i will put the status date as well as status code in the CUBE? then what will i do next?

by the way, what do you mean by end routine to look up from DSO?

[former_member182470 · Accepted solution]

If you don't want to show Status date in reports, then you can just get Status Code and Cust from DSO. You should use Cust to map with Cube Cust to update Status code field in cube.

Create a Self transformation from Cube to Cube and write a field level routine or end routine to update status code. Hope this is clear.

[Loed · Accepted solution]

Can you elaborate further mate? Sorry did not get yet the whole solution..=(

What do you mean by write a field level routine to update the status code?

I just thought of this from your idea:

1. I will add the status code in cube

2. I will make this field routine in the status code in cube

select max(status date) from dso into TEMP_DATE where payer dso = payer cube and status date <= 0calday.

select status code from dso into RESULT where payer dso = payer cube and status date = TEMP_DATE.

Are we on the same page?

[former_member182470 · Accepted solution]

Yep, we are on the same page. You can enhance your cube either by Status code alone or by boht code and date.

My idea in making a self loop is, you will get your raw data into cube without status code(will be in blank) via transformation/dtp  from the source of cube.

Create a one more transformation between Cube to cube and write an End routine(as I said in previous reply, please lookup from DSO here). Your result package will contain all your cube records and updates status code as per Cust joins. I can't help you with code. But the whole idea is my reply.

[Loed · Accepted solution]

What do you mean by self loop mate? Is it possible to have a transformation with same CUBE in source and target? Sorry mate did not get yet the idea..

[Loed]

Hi Suman and AL, it's already working!

THANKS a lot for your ideas/suggestions..=)

Regards,

Loed

[former_member182470]

Good to know that..

[anshu_lilhori]

Loed,If you are flexible with modeling level changes then i would suggest to stamp the values of status and status date against payer in cube.

Add the two objects ion cube.Do a lookup on the DSO from cube in End routine and based on payer fetch the value of status and status date against each payer.

Now once you have the complete data in the cube then at reporting level to get the correct status based on the validity dates you can refer my blog.

PS:If you need assistance in the coding part then let me know.

Regards,

AL

[Loed]

Hi AL,

May I know how will I relate your thread in my scenario? Sorry I don't get it..

Also, is my code just above your post correct regarding the ROUTINE that you are saying?

Thank you!

Loed

[anshu_lilhori]

Loed,I just gone through your requirement once again and i think this can be done at modeling level only.Yes my blog will not come into the picture.

And the condition to be checked should be

If payer  = payer and status date is less than equal to  0calday then pick the value of status code.

The whole code can be written in end routine only.Just need to add one status code in cube and then as per my previous reply do the lookup as suggested.

Regards,

AL

[Loed]

Hi AL,

Can you teach me how to the end routine? Did not yet try it..

How will I put this in end routine?

select max(status date) from dso into TEMP_DATE where payer dso = payer cube and status date <= 0calday.

select status code from dso into RESULT where payer dso = payer cube and status date = TEMP_DATE.

Is end routine better than field routine?

Thank you!

Loed