How to add new columns in script? Caculate date interval.

[#1]

Hello everyone,

I have a table describe the test, include 4 fields (ID, TestType, Result, TestDate). I want to add new field when etl which can show last test date and how many months from last test.

The source table has 4 columns.

How to add 2 new columns (red color) in script when ETL?

Thank you very much.

I will demo next week, Please give me some idea. Thanks again.

ID	TestType	Result	TestDate	lastTestDate	TestInterval（in month）
1	A	1223	1/1/2013
2	A	132	1/1/2013
3	A	12	1/1/2013
4	A	123	1/1/2013
5	A	234	1/1/2013
2	A	2	2/1/2013	1/1/2013	1
3	A	123	2/1/2013	1/1/2013	1
4	A	13	3/1/2013	1/1/2013	2
1	A	123	7/1/2013	1/1/2013	6
5	A	234	8/1/2013	1/1/2013	7
1	A	23	8/1/2013	7/1/2013	1
2	A	1231	9/1/2013	2/1/2013	7

[kaushiknsolanki]

Hi,

     Try this.

     Load *,interval(LastTestDate - TestDate,'D') as Days Interval

     From xyz;

Regards,

Kaushik Solanki

[#3]

Hi,

     try this,

1.  First  load this to get lastTestDate and TestInterval field:

     tempTable:

     LOAD

       *,

       Month(TestDate) - Month(lastTestDate) AS TestInterval;

     LOAD

       *,

       If(Peek('ID') = ID, Peek('TestDate')) AS lastTestDate

     Resident xyz

     Order By ID,TestDate;

2. After  this, load again to get result according to your Order By:

     LOAD

       *

     Resident tempTable

     Order By TestDate;

Regards,

Ashutosh

[#4]

The question is there is no lastTestDate field in the source table.

How to get the field lastTestDate?

[#5]

hi,

   Yes, I understand what  you are asking.

   Check again my script. In this script, first lastTestDate field will create and after that TestInterval filed will be create according to both date fields.

First try this script then let me know.

Thanks

Ashutosh

[mdmukramali]

Dear,

Kindly find the attachment.

I hope it will help you.

Thanks,

Mukram.

[#7]

I have tried your script. It works! Thank you and one more question

If TestType is A B C D.... I　want get lasttestDate depent on testType. How can I edit your script.

[mdmukramali]

Dear ,

can you try below script ,

First:

LOAD *

      Inline

[

ID,TestType ,Result ,TestDate

1,A,1223,1/1/2013

2,A,132,1/1/2013

3,A,12,1/1/2013

4,B,123,1/1/2013

5,A,234,1/1/2013

2,A,2,2/1/2013

3,A,123,2/1/2013

4,B,13,3/1/2013

1,A,123,7/1/2013

5,A,234,8/1/2013

1,A,23,8/1/2013

2,A,1231,9/1/2013

3,C,1456,3/5/2013

3,C,1343,5/11/2013

3,C,34,7/11/2013

];

Second:

  LOAD *,

                                      //     If(Peek('ID')= ID, Peek('TestDate'),'-') AS LastTestDate,

If(Peek('ID')= ID and Peek(TestType)=TestType, Peek('TestDate'),'-') AS LastTestDate,

                                     //     (Month(TestDate)-Month(If(Peek('ID') = ID, Peek('TestDate'),'-'))) as TimeInterval

  (Month(TestDate)-Month(If(Peek('ID')= ID and Peek(TestType)=TestType, Peek('TestDate'),'-'))) as TimeInterval

     Resident First

     Order By ID,TestDate;

     DROP Table First;

Thanks,

Mukram.

[#9]

Hi Mukram,

Thanks for your reply.

I have tried your script, The field TimeInterval has no value in any rows...

[#10]

Hi,

     first  clear my question. Suppose for ID = 1,  2 rows for testType A and 2 rows for testType B seperately. So you want max TestDate for testType A and Max testDate for testType B...am I right ?

Thanx,

Ashutosh