topic Re: Setting up a start range for a range table in QlikView

Setting up a start range for a range table

RogerG — Wed, 27 Jan 2021 22:00:03 GMT

if I have this table

Hello all, If I have a table

phases:
load * inline [
Order, Phase, phasedate
1, phase1, null()
2, phase2, null()
3, phase3, 7/1/2020
4, phase4, null()
5, phase5, null()
6, phase4, 9/1/2020
];
costs:
load * inline [
project, cost, costdate
p1, 1000, 6/1/2021

];

I can set Phase 3 start and finish dates (7/1/2020 - 8/31/2020), and phase 4 (9/1/2020 - 12/31/9999)

but I don't know how to set Phase 2 (1/1/1900 -6/30/2020)

any suggestions would be appreciated

Re: Setting up a start range for a range table

MayilVahanan — Thu, 28 Jan 2021 09:26:56 GMT

Hi @RogerG

Try like below

phases:
load * inline [
Order, Phase, phasedate
1, phase1, null()
2, phase2, null()
3, phase3, 7/1/2020
5, phase5, null()
6, phase4, 9/1/2020
];

Load *, if(phasedate <> 'null()', phasedate, if(Peek('endDate') = '12/31/9999' or phasedate='null()', '1/1/1900', Date(Peek('endDate')-1))) as startDate,
if(RowNo()= 1 or peek('startDate') = '1/1/1900', '12/31/9999', Date(peek('phasedate')-1)) as endDate Resident phases order by Phase desc;

DROP Table phases;

Re: Setting up a start range for a range table

RogerG — Fri, 29 Jan 2021 00:50:31 GMT

I thought of that, however I end up with

so what I tried was

phase

load *,

min(phaseDate) as minphase

resident phase

group by order

then

lowestPhases:

load *,

min(minphase)-1 as firstphase;

resident Phase;

put putting it all back in one table is a mess.