topic Re: How to add new columns in script? Caculate date interval. in QlikView

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

Wed, 13 Nov 2013 07:02:25 GMT

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

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

kaushiknsolanki — Wed, 13 Nov 2013 07:04:39 GMT

Hi,

Try this.

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

From xyz;

Regards,

Kaushik Solanki

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

Wed, 13 Nov 2013 08:10:52 GMT

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

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

Wed, 13 Nov 2013 08:30:28 GMT

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

How to get the field lastTestDate?

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

Wed, 13 Nov 2013 08:36:19 GMT

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

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

mdmukramali — Wed, 13 Nov 2013 08:42:48 GMT

Dear,

Kindly find the attachment.

I hope it will help you.

Thanks,

Mukram.

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

Wed, 13 Nov 2013 09:20:53 GMT

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.

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

mdmukramali — Wed, 13 Nov 2013 09:45:36 GMT

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.

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

Wed, 13 Nov 2013 09:58:41 GMT

Hi Mukram,

Thanks for your reply.

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

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

Wed, 13 Nov 2013 10:02:09 GMT

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

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

mdmukramali — Wed, 13 Nov 2013 10:03:26 GMT

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.

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

Wed, 13 Nov 2013 10:35:18 GMT

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

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

Wed, 13 Nov 2013 11:23:39 GMT

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

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

Fri, 15 Nov 2013 00:43:36 GMT

Hi Ashutosh,

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

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

Fri, 15 Nov 2013 00:57:40 GMT

Thanks for your qvw file.

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