Iter apply cost calendar range date

[#1]

Hi all,

I need to apply for each order the appropriate cost.

For semplicity, attach an easy script with the same logic

I have 2 table

The first table (calendar) that contain the date

The second table (cost) that contain the cost (date-range).

I do this in 4 step.

I belive that i can obtain the same result in 3 steps with ITER.

Load Calendar

Load Cost

Iter Calendar and apply appropriate cost but i can't find the right way to apply the cost

Someone can help me plese:

vStart=2013;

LET vActualYear = year(now());

LET Start = num(makedate($(vActualYear) ,01,01));

LET End   = num(makedate($(vActualYear)+1 ,12,31));

LET NumOfDays = End - Start + 1;

SET inv=1;

Date_src:

LOAD

  $(Start) + Rowno() -1 as DataGen

AUTOGENERATE $(NumOfDays);

Calendar:

Load

num#(Date(DataGen, 'YYYYMMDD'), 'YYYYMMDD') as DataInvoice

RESIDENT Date_src;

Cost:

LOAD * INLINE [
Data1, Data2, €uro
20130101,20130630,1000
20130701,20132131,2000];

T3:

NoConcatenate

LOAD *

Resident Calendar;

Left Join

LOAD *

Resident Cost;

NoConcatenate

  T4:

LOAD *

Resident T3

where DataInvoice=20130101;

DROP Table Calendar;

DROP table Cost;

Drop Table T3;

Exit script;

Many Thanks

Claudio

[Gysbert_Wassenaar]

Maybe this:

LET vStart = num(yearstart(Today()));
LET vEnd = num(floor(YearEnd(Today())));

Cost:
LOAD date#(Data1,'YYYYMMDD') as Data1, date#(Data2,'YYYYMMDD') as Data2, €uro  INLINE [
Data1, Data2, €uro
20130101,20130630,1000
20130701,20132131,2000];

CalendarWithCosts:
load $(vStart) + RecNo() -1 as DateInvoice
AutoGenerate $(vEnd) - $(vStart) + 1;

left join
IntervalMatch(DateInvoice)
load Data1, Data2 Resident Cost;

left join load * Resident Cost;

drop table Cost;
drop fields Data1, Data2;

[#3]

Try like below:

Cost:

LOAD Data1 + IterNo() -1  AS DateInvoice,

        €uro

While IterNo() <= (Data2 - Data1 +1 );

LOAD num(date(date#(Data1,'YYYYMMDD'))) as Data1, num(date(date#(Data2,'YYYYMMDD'))) as Data2, €uro  INLINE [ 

Data1, Data2, €uro 

20130101,20130630,1000 

20130701,20131231,2000]; 

[#4]

Hi Gysbert,

thank you for your replay

It works but include the same date two times   see the day 41300

[Gysbert_Wassenaar · Accepted solution]

Ah, a typo. Change the date format from YYYYDDMM to YYYYMMDD

[#6]

Great Gysbert

Many thanks for your help.

Merry Chrismas and happy new year

Claudio