Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

How to use calculated column to calculate another column..??

Q1: I am loading following columns in QV but it is showing Error as : Field Not Found :  Actual_Processed_Date

LOAD

SRID,       

   EnteredDate,

    ProcessedDate,

        if(Isnull(ProcessedDate),Today(),ProcessedDate) as Actual_Processed_Date,

NETWORKDAYS( EnteredDate,IF(IsNull(Actual_Processed_Date)='False',Actual_Processed_Date,TODAY()),[Network Days]) as Network_Days

FROM

[\\pcg\root\users\anushree.gore\Desktop\L2-Data.xlsx

] (ooxml, embedded labels, table is [L2-Data]);

Please Suggest a solution.. I want to use Actual_Processed_Date column for calcuting NetworkDays...

Q2: Also is there any way in Qlikview to to round off the date? For eg My actual value is 2013-01-03 00:49:08.287 and I want ot round it off as 1/3/13 12:00 AM. This is possible in Excel using RoundDown function... How can we do it in qlikview?

1 Solution

Accepted Solutions
Not applicable

Re: How to use calculated column to calculate another column..??

for round the date

date(floor('2013-01-03 00:49:08.287')).

OR

=DATE(floor('2013-01-03 00:49:08.287'),'DD/MM/YY')  & Time(floor('2013-01-03 00:49:08.287'))

View solution in original post

6 Replies
Not applicable

Re: How to use calculated column to calculate another column..??

for round the date

date(floor('2013-01-03 00:49:08.287')).

OR

=DATE(floor('2013-01-03 00:49:08.287'),'DD/MM/YY')  & Time(floor('2013-01-03 00:49:08.287'))

View solution in original post

MVP & Luminary
MVP & Luminary

Re: How to use calculated column to calculate another column..??

Q1: You can use a preceding load:

Load A,B, if(B>A,B,A) as C;

Load A, rangemax(10,D) as B

from ...somewhere...;

The result from the lower load is the input of the preceding load. So field B is created in the lower load and then available for calculations in the preceding load.

Q2: Use the floor function like vishwaranjan advises.

BTW, this isn't going to work: IF(IsNull(Actual_Processed_Date)='False'

IsNull returns either 0 or -1, never a string like 'False'. To test for nulls is usually better done like this: if(len(trim( MyField ))=0, ... , ... )


talk is cheap, supply exceeds demand
er_mohit
Honored Contributor II

Re: How to use calculated column to calculate another column..??

Try this

LOAD

SRID,      

   EnteredDate,

    ProcessedDate,

        if(Isnull(ProcessedDate),Today(),ProcessedDate) as Actual_Processed_Date,

NETWORKDAYS( EnteredDate,IF(IsNull(ProcessedDate)='False',ProcessedDate,TODAY()),[Network Days]) as Network_Days

FROM

[\\pcg\root\users\anushree.gore\Desktop\L2-Data.xlsx

] (ooxml, embedded labels, table is [L2-Data]);

Q2: try this

DATE(floor('2013-01-03 00:49:08.287'),'DD/MM/YY hh:mm TT')

Partner
Partner

Re: How to use calculated column to calculate another column..??

Hi ,

You can use presiding load

See the below syntex:

 

Load

*,

NETWORKDAYS(EnteredDate,IF(IsNull(Actual_Processed_Date)='False',Actual_Processed_Date,TODAY()),[Network Days]) as Network_Days;

LOAD



SRID,       

 
EnteredDate,

  
ProcessedDate,

    
if(Isnull(ProcessedDate),Today(),ProcessedDate) as Actual_Processed_Date 

FROM

[\\pcg\root\users\anushree.gore\Desktop\L2-Data.xlsx] (
ooxml, embedded labels, table is [L2-Data]);  

Not applicable

Re: How to use calculated column to calculate another column..??

LOAD

SRID,       

   EnteredDate,

    ProcessedDate,

        if(Isnull(ProcessedDate),Today(),ProcessedDate) as Actual_Processed_Date,

NETWORKDAYS( EnteredDate,IF(IsNull(if(Isnull(ProcessedDate),Today(),ProcessedDate))='False',if(Isnull(ProcessedDate),Today(),ProcessedDate),TODAY()),

[Network Days]) as Network_Days

FROM

[\\pcg\root\users\anushree.gore\Desktop\L2-Data.xlsx

] (ooxml, embedded labels, table is [L2-Data]);

Not applicable

Re: How to use calculated column to calculate another column..??

Hello Thanks for your reply.. But still there is a problom that it is showing me error.. Field Not Found Network Days because this filed is present in some other table...