topic Re: How to add calculated column using the calculated column in QlikView

How to add calculated column using the calculated column

Wed, 19 Apr 2017 14:10:47 GMT

Hello All

I want to create a calculated column from an calculated column.

Below is the example:

TEST:

Load [Name],

[Date of Birth],

if([Date of Birth]='','Null',[Date Of Birth]) as [DOB], /* First Calculated Column*/

timestamp([DOB],'YYYY') as 'Ýear', /* Second calculated column derived from First Calculated Column [Date of Birth] */

month([DOB]/3) as 'Quater' /* Third calculated column derived from First Calculated Column [Date of Birth] */

From

[abc/test.xlsx]

I am getting an error message as [DOB] not found. But if i remove the second and third calculated columns then no error the script executes successfully.

Re: How to add calculated column using the calculated column

Anonymous — Wed, 19 Apr 2017 15:57:51 GMT

LOAD

* ,

timestamp([DOB],'YYYY') as 'Ýear',

month([DOB]/3) as 'Quater' ;

Load [Name],

[Date of Birth],

if([Date of Birth]='','Null',[Date Of Birth]) as [DOB], /* First Calculated Column*/

[Date of Birth] ;

Re: How to add calculated column using the calculated column

Thu, 20 Apr 2017 06:39:21 GMT

Sorry, but this is not working correctly. Now I am not getting any error message but the data is not coming thru correctly.

This is the code I have used.....

LOAD
*,
Timestamp(DOB,'ÝYYY') as Year,
month(DOB/3) as Quater;

LOAD
Name,
Date as DOB
FROM

(ooxml, embedded labels, table is Sheet1);

O/p:

Re: How to add calculated column using the calculated column

Anonymous — Thu, 20 Apr 2017 06:44:10 GMT

... this is another question, you never said what you tried to achieve, so please explain...

Re: How to add calculated column using the calculated column

Thu, 20 Apr 2017 07:01:52 GMT

have to use ceil or dual function to get the quarter

something like ceil(month(dob)/3) as quarter or dual('q' & ceil(month(dob)/3), ceil(month(dob)/3)) as quarter

coming to the calculated fields, you cant use those in the same table. use resident load and use them

can you please share exactly what you trying to achieve here....