Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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;
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
];
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.
Hi
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