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

[mdmukramali]

Dear,

In your sample apps i used the same script i'm getting time interval values.

can you have look on the attachment of the sample file.

Thanks,

Mukram.

[#3]

I can't open the website and download the attachment. I will check this later. Thanks for your help.

[#4 · Accepted solution]

Hi,

     try this script

     LOAD

       *,

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

     LOAD

       *,

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

          Resident tempInterval

     Order By ID, TestType, TestDate;

I hope this will help you.

Thanks,

Ashutosh

[#5]

Hi Ashutosh,

Thank you for your help. Your script is wonderful:)

[#6]

Thanks for your qvw file.

I add order by ID, testType, testDate, it works:)