topic Creating a date range table from a single date column field in QlikView

Creating a date range table from a single date column field

RogerG — Thu, 29 Apr 2021 15:30:40 GMT

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

Re: Creating a date range table from a single date column field

RogerG — Fri, 30 Apr 2021 17:32:03 GMT

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 :)?

Re: Creating a date range table from a single date column field

yassinemhadhbi — Sun, 02 May 2021 22:36:30 GMT

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

Re: Creating a date range table from a single date column field

RogerG — Mon, 03 May 2021 15:50:37 GMT

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;