topic Re: DATE TABLE in QlikView

DATE TABLE

adiarnon — Thu, 05 Jun 2014 09:25:33 GMT

hi,

i have this table-

project	start date	finish date
A	1.1.14	3.1.14
B	2.1.14	5.1.14
C	15.1.14	16.1.14

and i want to create this table -

project	date
A	1.1.14
A	2.1.14
A	3.1.14
B	2.1.14
B	3.1.14
B	4.1.14
B	5.1.14
C	15.1.14
C	16.1.14

HOW CAN I DO THAT?

TNX,

ADI

Re: DATE TABLE

tresB — Thu, 05 Jun 2014 09:44:53 GMT

Load
project,
Date([start date]+IterNo()-1) as Date
While [start date]-1+iterno()<=[finish date];

LOAD project,
Date#([start date], 'DD.MM.YY') as [start date],
Date#([finish date],'DD.MM.YY') as [finish date]
FROM
[http://community.qlik.com/thread/120967]
(html, codepage is 1252, embedded labels, table is @1);

Re: DATE TABLE

its_anandrjs — Thu, 05 Jun 2014 10:02:28 GMT

By Iterno() function you can create missing dates see the load script for that or you can create another table and use that table for new date columns if old table not required you can drop them

Raw:

LOAD

project,

Date(Date#([start date], 'D.M.YY')) as [start date],

Date(Date#([finish date],'D.M.YY')) as [finish date];

LOAD * Inline

[

project,start date, finish date

A, 1.1.14, 3.1.14

B, 2.1.14, 5.1.14

C, 25.1.14,3.2.14

];

NewTable:

Load project, Date([start date]+IterNo()-1) as date

Resident Raw

While [start date] - 1 + iterno() <= [finish date];

//DROP Table Raw;

Re: DATE TABLE

Thu, 05 Jun 2014 10:21:55 GMT

let vstartdate = floor(date#('20101001','YYYYMMDD'));

let vEnddate = floor(monthend(today()));

Temp:

LOAD $(vstartdate) + RowNo() as temp_date

AutoGenerate($(vEnddate) - $(vstartdate));

MAP_FISCAL_MONTH:

Mapping LOAD * Inline

[

MonthName, MonthNo

Oct, 01

Nov, 02

Dec, 03

Jan, 04

Feb, 05

Mar, 06

Apr, 07

May, 08

Jun, 09

Jul, 10

Aug, 11

Sep, 12

];

Re: DATE TABLE

Josh_Good — Thu, 05 Jun 2014 11:28:21 GMT

Create a master calendar that uses the minimum start date and the maximum end date as the range of dates. See this Re: Creating A Master Calendar. But in your case lines 7-11 would be:

Temp:

Load

min(Start Date) as minDate,

max(End Date) as maxDate

Resident <tablename>;

Then use the Intervalmatch function to link you calendar to your data table.

Re: DATE TABLE

Thu, 05 Jun 2014 11:41:55 GMT

I used Interval Match it worked perfectly

Please see below blog

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

thanks and regards

Padma