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