Hi Team, I need to create new table(Expressions) from a existing table

[#1]

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.

[MK_QSL]

Do you want to do this in script or at front end?

[#3]

It would be best , if I can do it in Script, Please guide me Manish !!!

[MK_QSL · Accepted solution]

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;

[#5]

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

[MK_QSL]

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.