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