Jump to content

This topic is 7772 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted

This works:

Case

(TYPE="AEWR" ,PRICE LIST 2003 DB::AEWR,

TYPE = "DEWR",PRICE LIST 2003 DB::DEWR,

TYPE = "DJFK",PRICE LIST 2003 DB::DJFK,

TYPE = "AJFK",PRICE LIST 2003 DB::AJFK,

TYPE = "DLAG",PRICE LIST 2003 DB::DLAG,

TYPE = "ALAG",PRICE LIST 2003 DB::ALAG)

but

this doesn't work

Case

(DATE < 11/14/2003 + TYPE="AEWR",PRICE LIST 2003 DB::AEWR,

DATE < 11/14/2003 + TYPE = "DEWR",PRICE LIST 2003 DB::DEWR,

DATE < 11/14/2003 + TYPE = "DJFK",PRICE LIST 2003 DB::DJFK,

DATE < 11/14/2003 + TYPE = "AJFK",PRICE LIST 2003 DB::AJFK,

DATE < 11/14/2003 + TYPE = "DLAG",PRICE LIST 2003 DB::DLAG,

DATE < 11/14/2003 + TYPE = "ALAG",PRICE LIST 2003 DB::ALAG)

and

this doesn't work

Case

(TYPE="AEWR" AND DATE <= 11/14/2003 ,PRICE LIST 2003 DB::AEWR,

TYPE = "DEWR" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::DEWR,

TYPE = "DJFK" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::DJFK,

TYPE = "AJFK" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::AJFK,

TYPE = "DLAG" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::DLAG,

TYPE = "ALAG" AND DATE <= 11/14/2003,PRICE LIST 2003 DB::ALAG)

the define fields accepts all of these calculations but the ones that do not work return no value whatsoever.

What an I missing?

I want to change my price list from 11/14/2003 while keeping the old price list the same as before that date for billing purposes.

Thank you,

Mitchell

Posted

Try Date( 11, 14, 2003 ) instead of 11/14/2003. '+' definitely won't work for logic testing. And in your third section, you have no result if TYPE is any of the possibilities listed and DATE > Date( 11, 14, 2003 ), thus no value returned.

Try something more like:

Case(

TYPE = "AEWR", Case( DATE < Date( 11, 15, 2003 ), PRICE LIST 2003 DB::AEWR, PriceToUseOtherwise ), TYPE = "DEWR", Case( DATE < Date( 11, 15, 2003 ), ...

)

NOTE: I use Date( 11, 15, 2003 ) to avoid using 'less than or equal to' syntax. I think it has more clarity that way. It's just a personal preference.

Posted

The reason it "doesn't" work is probably because 11/14/2003...in other words 11 divided by 14 divided by 2003 (answer: 0.0003922!)

(PS It must be really irritating when Bob tidies your code! No offence Bob - have to admit it does look much nicer)

Posted

While tidier, I don't think it encompasses all the necessary information. You still need what the result would be for each TYPE, if the Date were after 11/14/2003, which I added since Mitchell appeared to omit it originally. You have it defaulting to "", which I don't think is what he's needing. Of course this is all based on interpretation of omitted data, crazy.gif but if I'm correct, then you'll need to nest Case()s either way.

Posted

I guess so, but the date condition is DATE<Date(11,15,2003) in every case. If the date test changes , then a single case statement could be used:

Case(TYPE="AEWR" and DATE < Date(11,15,2003),PRICE LIST 2003 DB::AEWR,

Posted

Exactly, that's his problem. He doesn't want it to return "". So we need something like:

Case(DATE < Date(11,15,2003),

Case(TYPE = "AEWR" ,PRICE LIST 2003 DB::AEWR,

TYPE = "DEWR" ,PRICE LIST 2003 DB::DEWR,

TYPE = "DJFK" ,PRICE LIST 2003 DB::DJFK,

TYPE = "AJFK" ,PRICE LIST 2003 DB::AJFK,

TYPE = "DLAG" ,PRICE LIST 2003 DB::DLAG,

TYPE = "ALAG" ,PRICE LIST 2003 DB::ALAG),

Case(TYPE = "AEWR", aewrprice,

TYPE = "DEWR", dewrprice,

TYPE = "DJFK", djfkprice,

TYPE = "AJFK", ajfkprice,

TYPE = "DLAG", dlagprice,

TYPE = "ALAG", alagprice)

)

Posted

MITCHELL said:

The define fields accepts all of these calculations but the ones that do not work return no value whatsoever.

What an I missing?

I want to change my price list from 11/14/2003 while keeping the old price list the same as before that date for billing purposes.

I believe that would be it right there. smile.gif

Posted

MITCHELL said:

I want to change my price list from 11/14/2003 while keeping the old price list the same as before that date for billing purposes.

And now that the calc is solved, is it really the way to lock price lookup ? Because I have the feeling that's what's inside here. mad.gif

This topic is 7772 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.