Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to create dates Dates in a Range?

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.

11-11-2011 10-08-45 AM.jpg

I have the following data for example:

DatefromDataToProductvalue
Jan-2011Mar-2011a10
Feb-2011Mar-2011b20
Apr-2011Jun-2011a12
May-2011Aug-2011b19

The results expected are the following:

MonthProductValue
Jan-11a10
Feb-11a10
Mar-11a10
Apr-11a12
May-11a12
Jun-11a12
Feb-11b20
Mar-11b20
Apr-11b20
May-11b19
Jun-11b19
Jul-11b19
Aug-11b19

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

Thanks in advance,

Leandro Duarte

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

How to create dates Dates in a Range?

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

6 Replies
MVP
MVP

How to create dates Dates in a Range?

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

Hope this helps,

Stefan

Not applicable

How to create dates Dates in a Range?

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

MVP
MVP

How to create dates Dates in a Range?

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

Not applicable

How to create dates Dates in a Range?

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,

MVP
MVP

How to create dates Dates in a Range?

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

MVP
MVP

Re: How to create dates Dates in a Range?

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

Community Browser