Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Ah, a typo. Change the date format from YYYYDDMM to YYYYMMDD
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;
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];
Hi Gysbert,
thank you for your replay
It works but include the same date two times see the day 41300
Ah, a typo. Change the date format from YYYYDDMM to YYYYMMDD
Great Gysbert
Many thanks for your help.
Merry Chrismas and happy new year
Claudio