date between X and Y

[#1]

Hi I have a table like this:

FOM_DAT	FOM_POSTNR	KLIENT_NR	LAND_KOD_VARM	TOM_DAT	TOM_POSTNR
1009	83000	108	SE	0220	99999
0107	52000	108	SE	1115	82999
0114	10000	108	SE	1115	19999
0128		108	DK	1101	99999
0128	20000	108	SE	1101	51999
0506		108	FI	1122	99999

I want to add a cost of X if an order have a (1. Order date between FOM_DAT and TOM_DAT ) and (2.have an postal number between FOM_POSTNR and TOM POST_NR) and (3. Country code = LAND_KOD_VARM ).

Example:

Order 1234

Postal number: 24560

Order date 2009-12-01

Country code: SE

Then a cost of X to the order

And example

Order 4321

Postal number 24560

Order date: 2010-06-01

Country code: SE

No extra cost.

Is this possible?

[tabletuner]

I think you can use the if and the intervalmatch functions in your case.

[#3]

Hi I'm completely lost I have tried to read on IntervalMatch on internet and the reference manual but I cannot find anything that helps me.

So I hope someone can give me a hint?

I have tried this Script and formula but it does not work.

LOAD

"ORDER_DATE",

date(ORDER_DATE,'MMDD') as ORDER_SHORT_DATE,

PNR,

"KLIENT_NR",

CONTRY_CODE,

ORD_NR,

;

SQL SELECT *

FROM "ORDER" where KLIENT_NR = '108';

VARM:

LOAD date(FOM_DAT,'MMDD') as FOM_DAT,

FOM_POSTNR,

KLIENT_NR as KLIENT_NR_VARM,

CONTRY_CODE_V,

date(TOM_DAT,'MMDD') as TOM_DAT,

TOM_POSTNR

FROM

Värme.xls

(biff, embedded labels, table is Sheet1$);

join (VARM) IntervalMatch ( PNR ) LOAD FOM_POSTNR, TOM_POSTNR Resident VARM;

join (VARM) IntervalMatch (ORDER_SHORT_DATE) LOAD FOM_DAT, TOM_DAT Resident VARM;

and then I create a variable called "Värme Tillägg"

And a formula:

sum(if(ORDER_SHORT_DATE>=FOM_DAT and ORDER_SHORT_DATE<=TOM_DAT and CONTRY_CODE_V = CONTRY_CODE and PNR>=FOM_POSTNR and PNR<=TOM_POSTNR,"Värme Tillägg",0))

But this doesn't work.

[#4]

Maybe something like below using an input variable for the additional cost

sum(if((FOM_DAT>X AND TOM_DAT<Y) AND (FOM_POSTNR>X AND TOM_POSTNR<Y) AND match(LAND_KOD_VARM , 'SE'), OrderValue + $(AdditionalCost))