topic Re: Iter apply cost calendar range date in QlikView

Iter apply cost calendar range date

Thu, 19 Dec 2013 14:42:27 GMT

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

Re: Iter apply cost calendar range date

Gysbert_Wassenaar — Thu, 19 Dec 2013 15:46:47 GMT

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;

Re: Iter apply cost calendar range date

Thu, 19 Dec 2013 17:01:19 GMT

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];

Re: Iter apply cost calendar range date

Fri, 20 Dec 2013 12:02:55 GMT

Hi Gysbert,

thank you for your replay

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

Re: Iter apply cost calendar range date

Gysbert_Wassenaar — Fri, 20 Dec 2013 15:43:46 GMT

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

Re: Iter apply cost calendar range date

Sat, 21 Dec 2013 17:14:52 GMT

Great Gysbert

Many thanks for your help.

Merry Chrismas and happy new year

Claudio