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

[#1]

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?

[#2 · Accepted solution]

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'))

[Gysbert_Wassenaar]

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, ... , ... )

[er_mohit]

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')

[kumarnatarajan]

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]);  

[#6]

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]);

[#7]

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...