Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Iter apply cost calendar range date


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

1 Solution

Accepted Solutions

Re: Iter apply cost calendar range date

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


talk is cheap, supply exceeds demand
5 Replies

Re: Iter apply cost calendar range date

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Iter apply cost calendar range date

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

Not applicable

Re: Iter apply cost calendar range date

Hi Gysbert,

thank you for your replay

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

Cattura.PNG.png

Re: Iter apply cost calendar range date

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


talk is cheap, supply exceeds demand
Not applicable

Re: Iter apply cost calendar range date

Great Gysbert

Many thanks for your help.

Merry Chrismas and happy new year

Claudio

Community Browser