Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
H Team,
So, Here is sample data
Id | ErrorType | Date | Status |
1 | A | 1/1/2017 | Unresolved |
1 | A | 1/2/2017 | Resolved |
1 | A | 1/3/2017 | Unresolved |
1 | A | 1/4/2017 | Unresolved |
1 | A | 1/5/2017 | Resolved |
1 | A | 1/6/2017 | Unresolved |
1 | A | 1/7/2017 | Resolved |
1 | B | 1/7/2017 | Unresolved |
As you can see , Id 1 has 2 type of errors, but here error has multiple entries, it opened on 1/1/2017 but get resolved on 1/2/2017, it again reopened on 1/3/2017 and then resolved on 1/5/2017 and again it get reopened on 1/6/2017 and then resolved on 1/7/2017
I want a table from this flat file that will below result
Id | ErrorType | Start Date | Resolve Date | Status |
1 | A | 1/1/2017 | 1/2/2017 | Resolved |
1 | A | 1/3/2017 | 1/5/2017 | Resolved |
1 | A | 1/6/2017 | 1/7/2017 | Resolved |
1 | B | 1/7/2017 | Unresolved |
So I have created 2 new fields, Start Dare and Resolved Date, So ErrorType A was opened 3 times so 3 rows with open date(Start Date) and ResolvedDate.
I am sure there would be some easy script but mean time you can use this..
Data:
Load * Inline
[
Id ErrorType Date Status
1 A 1/1/2017 Unresolved
1 A 1/2/2017 Resolved
1 A 1/3/2017 Unresolved
1 A 1/4/2017 Unresolved
1 A 1/5/2017 Resolved
1 A 1/6/2017 Unresolved
1 A 1/7/2017 Resolved
1 B 1/7/2017 Unresolved
] (delimiter is \t);
Temp1:
Load
RowNo() as ID1,
Id,
ErrorType,
Date,
Status,
If(Status = Previous(Status),1) as Flag,
If(Status = 'Unresolved' and Previous(Status) <> 'Unresolved',Date,Null()) as [Start Date]
Resident Data
Order By Id, ErrorType, Date Asc;
Drop Table Data;
Temp2:
Load
RowNo() as ID2,
*,
If(RowNo() = 1, Null(),If(Peek(Status) = 'Resolved', Peek(Date))) as [End Date]
Resident Temp1
Where Flag <> 1
Order By ID1 Desc;
Drop Table Temp1;
Drop Field Status;
NoConcatenate
Final:
Load
*,
If(RowNo() = 1, 'Unresolved','Resolved') as Status
Resident Temp2
Where Not IsNull([Start Date]);
Drop Table Temp2;
Drop Fields Date, ID1, ID2, Flag;
Do you want to do this in script or at front end?
It would be best , if I can do it in Script, Please guide me Manish !!!
I am sure there would be some easy script but mean time you can use this..
Data:
Load * Inline
[
Id ErrorType Date Status
1 A 1/1/2017 Unresolved
1 A 1/2/2017 Resolved
1 A 1/3/2017 Unresolved
1 A 1/4/2017 Unresolved
1 A 1/5/2017 Resolved
1 A 1/6/2017 Unresolved
1 A 1/7/2017 Resolved
1 B 1/7/2017 Unresolved
] (delimiter is \t);
Temp1:
Load
RowNo() as ID1,
Id,
ErrorType,
Date,
Status,
If(Status = Previous(Status),1) as Flag,
If(Status = 'Unresolved' and Previous(Status) <> 'Unresolved',Date,Null()) as [Start Date]
Resident Data
Order By Id, ErrorType, Date Asc;
Drop Table Data;
Temp2:
Load
RowNo() as ID2,
*,
If(RowNo() = 1, Null(),If(Peek(Status) = 'Resolved', Peek(Date))) as [End Date]
Resident Temp1
Where Flag <> 1
Order By ID1 Desc;
Drop Table Temp1;
Drop Field Status;
NoConcatenate
Final:
Load
*,
If(RowNo() = 1, 'Unresolved','Resolved') as Status
Resident Temp2
Where Not IsNull([Start Date]);
Drop Table Temp2;
Drop Fields Date, ID1, ID2, Flag;
Hi Manish,
Your solution is elegant !!
Can you please help me with End Date Logic. I am still not able to understand it.
Can you please help me how peek function helped in capturing last date,
Again, Thank you very much !!!
Regards
Siddharth
You can do below steps to learn what I have done
1) Load only Data table and use table box to load every field in it.
2) Now load until Drop Table Data; and do the same.
3) Now again load until Drop Table Temp1; and do the same
4) Now load full..
This way you will understand step by step what I have done... ! If you still have any issue in understanding my code, will surely help you.