fill out missing dates

[unkisoN]

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 !

[lironbaram · Accepted solution]

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;

[Taoufiq_Zarra]

@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:

[unkisoN]

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.