How to create dates Dates in a Range?

[#1]

Dear Colleagues,

I need a help to create in a easy way period of datas for a price range table.

I'm using the solution Interval match with a calendar, but I would like to create it in the same table, today it is creating a $Syn Table.

I have the following data for example:

Datefrom	DataTo	Product	value
Jan-2011	Mar-2011	a	10
Feb-2011	Mar-2011	b	20
Apr-2011	Jun-2011	a	12
May-2011	Aug-2011	b	19

The results expected are the following:

Month	Product	Value
Jan-11	a	10
Feb-11	a	10
Mar-11	a	10
Apr-11	a	12
May-11	a	12
Jun-11	a	12
Feb-11	b	20
Mar-11	b	20
Apr-11	b	20
May-11	b	19
Jun-11	b	19
Jul-11	b	19
Aug-11	b	19

I really appreciate if somebody can help me to do it smartelly.  

Thanks in advance,

Leandro Duarte

[swuehl]

You should be able to just inner join the two tables, thus removing the synthetic key.

Hope this helps,

Stefan

[#3]

Hi Stefan,

I'm using the Inner Join, but it continue to appers.

Prices (is the table with the price range).

Month2 (is the table of Month available)

////

inner join (Month2)

INTERVALMATCH (Date)

LOAD

Datefrom,

Dateto

RESIDENT Prices;

/////

Based on this script, it continue to create the $Syn Table

[swuehl]

Yes, sorry, I intended to suggest an additional join, like

Values:

LOAD Date#(Datefrom,'MMM-YYYY') as Datefrom,

     Date#(DataTo,'MMM-YYYY') as DataTo,

     Product,

     value

FROM

[http://community.qlik.com/thread/38834?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

CalendarWithProducts:

LOAD Date(Date#(Month,'MMM-YY'),'MMM-YYYY') as Month ,

     Product

FROM

[http://community.qlik.com/thread/38834?tstart=0]

(html, codepage is 1252, embedded labels, table is @2, filters(

Remove(Col, Pos(Top, 3))

));

inner join (CalendarWithProducts) IntervalMatch (Month, Product)  LOAD Datefrom, DataTo, Product resident Values;

left join (CalendarWithProducts) LOAD * resident Values;

drop fields Datefrom, DataTo;

drop table Values;

Hope this helps,

Stefan

[#5]

thank you very much!

You are helping a lot,

In my case the table the table CalendarWithProducts only has Dates (months to be precise).

Would be possible to do it without the Products?

Thanks in advance,

[swuehl · Accepted solution]

Yes, should be possible, maybe like this:

Values:

LOAD Date#(Datefrom,'MMM-YYYY') as Datefrom,

     Date#(DataTo,'MMM-YYYY') as DataTo,

     Product,

     value

FROM

[http://community.qlik.com/thread/38834?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

CalendarWithProducts:

LOAD Date(Date#(Month,'MMM-YY'),'MMM-YYYY') as Month

//     ,Product

FROM

[http://community.qlik.com/thread/38834?tstart=0]

(html, codepage is 1252, embedded labels, table is @2, filters(

Remove(Col, Pos(Top, 3))

));

inner join (CalendarWithProducts) IntervalMatch (Month)  LOAD Datefrom, DataTo resident Values;

left join (CalendarWithProducts) LOAD * resident Values;

Please note that your Months need to have numerical representation, I think it would be best to use Dates like above formatted with Month name or year month as wanted.

Regards,

Stefan

[johnw]

I believe it would be simpler to use a while loop.  See script and attached.

LOAD
addmonths(date#(DateFrom,'MMM-YYYY'),iterno()-1) as Month
,Product
,Value
INLINE [
DateFrom, DateTo, Product, Value
Jan-2011, Mar-2011, a, 10
Feb-2011, Mar-2011, b, 20
Apr-2011, Jun-2011, a, 12
May-2011, Aug-2011, b, 19
] WHILE addmonths(date#(DateFrom,'MMM-YYYY'),iterno()-1) <= date#(DateTo,'MMM-YYYY');