topic How to create dates Dates in a Range? in QlikView

How to create dates Dates in a Range?

Fri, 11 Nov 2011 09:27:32 GMT

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

How to create dates Dates in a Range?

swuehl — Fri, 11 Nov 2011 09:45:55 GMT

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

Hope this helps,

Stefan

How to create dates Dates in a Range?

Fri, 11 Nov 2011 10:31:37 GMT

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

How to create dates Dates in a Range?

swuehl — Fri, 11 Nov 2011 10:33:41 GMT

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

How to create dates Dates in a Range?

Fri, 11 Nov 2011 12:20:09 GMT

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,

How to create dates Dates in a Range?

swuehl — Fri, 11 Nov 2011 12:53:10 GMT

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

Re: How to create dates Dates in a Range?

johnw — Sat, 12 Nov 2011 01:09:10 GMT

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');