Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
RogerG
Creator
Creator

Creating a date range table from a single date column field

Hello all,

I am trying to create a table with a start and finish date for specified rows, I am at a loss on how to go about it

I have

Test:
load * inline [
	ID, includeOrder, Name, thedate
	1, 1, ABC, 1/1/2020
	2, , ABC, 1/1/2019
	3, 2, BCD, 2/2/2020
	4, 3, CDE, 1/1/2020
	4, 3, CDE2, 1/1/2020
];


NoConcatenate
test1:
load ID,  min(thedate) as mindate
resident Test
group by ID;
left join (Test)
load *
resident test1;
	
drop table test1;

from the input table not all ID's are to be in the final output.  thedate column should specify the start of the include order, then the peek(includeOrder) should be it's end date.

I need all the values in the table, so I cannot lose ID 2 in this example.

I also need to eliminate one of the IncludeOrder 3's that are present.  one per Include order.

 

any help is appreciated

3 Replies
RogerG
Creator
Creator
Author

Tried to simplify it by removing all the uneeded records into a temp table, then added this after the initial load, but the Peek function is not returning my value

right join (Test) //get all known milestones		
	load ID, 
		includeOrder,
		thedate as CTPhaseStart,
		peek('thedate') as CTPhaseEnd
	RESIDENT Test
	Order by ID, includeOrder desc;

 

However the Peek cannot find the next value.

Anyone able to help :)?

yassinemhadhbi
Creator II
Creator II

Can you simplfy what do you want to achieve by putting the input and the desired output in excel file so that we can understand what you want to achieve

Best Regards
Yassine Mhadhbi
RogerG
Creator
Creator
Author

I figured out what I did wrong.  I didn't include thedate as one of the selection criteria, therefore peek had nothing to peek into

the correct answer is 

right join (Test) //get all known milestones		
	load ID, 
		includeOrder,
                thedate, // need this to be able to peek at it
		thedate as CTPhaseStart,
		peek('thedate') as CTPhaseEnd
	RESIDENT Test
	Order by ID, includeOrder desc;