topic Re: fill out missing dates in QlikView

fill out missing dates

unkisoN — Fri, 20 Nov 2020 18:58:42 GMT

Hi,

i have extracted a changelog table of the status from the Items:

ChangeLogDate	ItemNo	Status
16.06.2017	-PF000041	Active
28.06.2017	-PF000041	Blocked
11.07.2017	-PF000041	Active
13.07.2017	-PF000041	Blocked
04.10.2017	-PF000041	Active
04.10.2017	-PF000041	Blocked
06.04.2017	-PF000042	Active
08.05.2017	-PF000042	Blocked
16.06.2017	-PF000042	Active
28.06.2017	-PF000042	Blocked
30.06.2017	-PF000042	Active
05.07.2017	-PF000042	Blocked
11.07.2017	-PF000042	Active
13.07.2017	-PF000042	Blocked
04.10.2017	-PF000042	Active

What I need is:

The dates between the changes
The Status should be taken for the missing dates from the latest change

How it should look like:

ChangeLogDate	ItemNo	Status
16.06.2017	-PF000041	Active
17.06.2017	-PF000041	Active
18.06.2017	-PF000041	Active
19.06.2017	-PF000041	Active
20.06.2017	-PF000041	Active
21.06.2017	-PF000041	Active
22.06.2017	-PF000041	Active
23.06.2017	-PF000041	Active
24.06.2017	-PF000041	Active
25.06.2017	-PF000041	Active
26.06.2017	-PF000041	Active
27.06.2017	-PF000041	Active
28.06.2017	-PF000041	Blocked
29.06.2017	-PF000041	Blocked
30.06.2017	-PF000041	Blocked
01.07.2017	-PF000041	Blocked
02.07.2017	-PF000041	Blocked
03.07.2017	-PF000041	Blocked
04.07.2017	-PF000041	Blocked

I have tried a few solutions from the community with the Peek function but unfortunatelly nothing helped 😞

Thank you !

Re: fill out missing dates

lironbaram — Thu, 19 Nov 2020 08:45:39 GMT

hi
this script should give you your solution

DataTemp: load * inline [ ChangeLogDate, ItemNo, Status 16/06/2017, -PF000041, Active 28/06/2017, -PF000041, Blocked 11/07/2017, -PF000041, Active 13/07/2017, -PF000041, Blocked 04/10/2017, -PF000041, Active 04/10/2017, -PF000041, Blocked 06/04/2017, -PF000042, Active 08/05/2017, -PF000042, Blocked 16/06/2017, -PF000042, Active 28/06/2017, -PF000042, Blocked 30/06/2017, -PF000042, Active 05/07/2017, -PF000042, Blocked 11/07/2017, -PF000042, Active 13/07/2017, -PF000042, Blocked 04/10/2017, -PF000042, Active]; DataTemp2: load *, if(Previous(ItemNo)=ItemNo,previous(ChangeLogDate),date('31/12/2017')) as endDate Resident DataTemp order by ItemNo,ChangeLogDate Desc; drop Table DataTemp; Data: load *, Date(ChangeLogDate +IterNo()-1) as dateList Resident DataTemp2 while Date(ChangeLogDate +IterNo()-1) < endDate; drop Table DataTemp2;

Re: fill out missing dates

Taoufiq_Zarra — Thu, 19 Nov 2020 08:49:58 GMT

@unkisoN are you looking for ?

Data: LOAD rowno() as IDtmp,Date#(ChangeLogDate,'DD.MM.YYYY') as ChangeLogDate, ItemNo, Status INLINE [ ChangeLogDate, ItemNo, Status 16.06.2017, -PF000041, Active 28.06.2017, -PF000041, Blocked 11.07.2017, -PF000041, Active 13.07.2017, -PF000041, Blocked 04.10.2017, -PF000041, Active 04.10.2017, -PF000041, Blocked 06.04.2017, -PF000042, Active 08.05.2017, -PF000042, Blocked 16.06.2017, -PF000042, Active 28.06.2017, -PF000042, Blocked 30.06.2017, -PF000042, Active 05.07.2017, -PF000042, Blocked 11.07.2017, -PF000042, Active 13.07.2017, -PF000042, Blocked 04.10.2017, -PF000042, Active ]; Tmp: noconcatenate load *,if(ItemNo=peek(ItemNo),Date(peek(ChangeLogDate)-1),ChangeLogDate) as MaxChangeLogDateTmp; load * resident Data order by IDtmp DESC; drop table Data; output: noconcatenate load * resident Tmp order by IDtmp; left join load ChangeLogDate, Date(ChangeLogDate + IterNo() - 1) as NewChangeLogDate resident Tmp While ChangeLogDate + IterNo() -1 <= MaxChangeLogDateTmp; drop table Tmp; drop fields MaxChangeLogDateTmp,ChangeLogDate;

output:

Re: fill out missing dates

unkisoN — Wed, 24 Feb 2021 14:55:37 GMT

Hi @lironbaram

i have the same Table

But now i have a new field, when the Item was created.

LOAD * INLINE [
CreationDate, ItemNo
01.01.2015, -PF000041
01.06.2015, -PF000042
01.08.2015, -PF000043
01.10.2016, -PF000044
];

Now i have to adjust the previous script so that if i select example Year 2014.

The system should tell me that Items which are created after that it should show up as "not created".

And the first Active from the previous script should start with the creation date.

I hope you can help me with this.