Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

H Team,

So, Here is sample data

   

IdErrorTypeDateStatus
1A1/1/2017Unresolved
1A1/2/2017Resolved
1A1/3/2017Unresolved
1A1/4/2017Unresolved
1A1/5/2017Resolved
1A1/6/2017Unresolved
1A1/7/2017Resolved
1B1/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

     

IdErrorTypeStart DateResolve DateStatus
1A1/1/20171/2/2017Resolved
1A1/3/20171/5/2017Resolved
1A1/6/20171/7/2017Resolved
1B1/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.

1 Solution

Accepted Solutions

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

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 Replies

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

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

Not applicable

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

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

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

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;

Not applicable

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

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

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

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. 

Community Browser