Nested If Functions

[lucasdavis500]

I'm having trouble with qlikview resolving the following code:

EDIT: adding my full script:

//CET Data - Direct Disputes

IF NOT ISNULL(QVDCreateTime('$(vQVDPath)CETData.qvd')) THEN

LoadTime_CET:

LOAD MAX(DateCreated) AS DateCreated FROM $(vQVDPath)CETData.qvd (QVD) ;

LET vCETLastExecTime= chr(39) & DATE(PEEK('DateCreated',0,'LoadTime_CET')) & chr(39);

DROP TABLE LoadTime_CET;
END IF ;

CET:
LOAD
Pkey,
State,
AccountType,
DisputeType,
TaxID,
Mode,
AssignedTo,
ResolvedBy,
DATE(DateResolved) AS DateResolved,
DATE(DateCreated) AS DateCreated,
ProductLine,
CreditDisputeType,
IF(Mode='e-Oscar',0,1) AS [Not_e-Oscar_Dum],
DATE(DateCreated + 30) AS [Due Date],
MONTH(DATE(DateCreated)) AS [Month Created],
YEAR(DATE(DateCreated)) AS [Year Created],
'Q' & CEIL(MONTH(DateCreated) / 3) AS Quarter,
IF(DateResolved='Null',IF(MAX(DateCreated)-DateCreated>30,'Outstanding >30', 'Outstanding <30'),DateResolved - DateCreated) AS ResponseTime


FROM
[$(vPathname)CET_$(vCETDataDate).xlsx]
(ooxml, embedded labels, table is Sheet1)

WHERE DateCreated >= $(vCETLastExecTime) AND DateCreated < $(vExecTime);

IF NOT ISNULL(QVDCREATETIME('$(vQVDPath)CETData.qvd')) THEN

CONCATENATE(CET)
LOAD
Pkey,
State,
AccountType,
DisputeType,
TaxID,
Mode,
AssignedTo,
ResolvedBy,
DateResolved,
DateCreated,
ProductLine,
CreditDisputeType,
IF(Mode='e-Oscar',0,1) AS [Not_e-Oscar_Dum],
DATE(DateCreated + 30) AS [Due Date],
MONTH(DATE(DateCreated)) as [Month Created],
YEAR(DATE(DateCreated)) as [Year Created],
'Q' & CEIL(MONTH(DateCreated) / 3) AS Quarter,
IF(DateResolved='Null',IF(MAX(DateCreated)-DateCreated>30,'Outstanding >30', 'Outstanding <30'),DateResolved - DateCreated) AS ResponseTime

FROM
$(vQVDPath)CETData.qvd (QVD)

WHERE NOT(EXISTS (Pkey)) ;
END IF

IF NoOfRows('CET') > 0 THEN

STORE CET INTO $(vQVDPath)CETData.qvd (QVD) ;

//DROP TABLE CET ;
END IF ;

I can't figure out why QlikView thinks this code is invalid, here's the error in my log:

Invalid expression

Error: Invalid expression

Basically, I want Qlikview to take the Max DateCreated and then subtract that value from each DateCreated instance in every observation, given that DateResolved is NULL, otherwise just do DateResolved - DateCreated...

[Gysbert_Wassenaar]

My guess is that you didn't add a group by clause to your load statement that includes all the fields that you didn't use in aggregation functions like Max.

And even if you do you probably won't get the result you're after. Perhaps you should create a temporary table with just the max(DateCreated) values per ... whatever dimensions you need them aggregated over. Then join that table with the original table so you can compare the DateCreated value with the aggregated max(DateCreated) values in the if statement.

[sunny_talwar]

You are using an aggregation in your load statement which usually requires you to use Group By. But since you are trying to do Max(DateCreated) - DateCreated, You probably would need to make few modifications before this will work. Can you share your complete script here?

[sunny_talwar · Accepted solution]

May be try this:

//CET Data - Direct Disputes

IF NOT ISNULL(QVDCreateTime('$(vQVDPath)CETData.qvd')) THEN

  LoadTime_CET:

  LOAD MAX(DateCreated) AS DateCreated

  FROM $(vQVDPath)CETData.qvd (QVD);

  LET vCETLastExecTime= chr(39) & DATE(PEEK('DateCreated',0,'LoadTime_CET')) & chr(39);

  DROP TABLE LoadTime_CET;

ENDIF;

CET:

LOAD Pkey,

  State,

  AccountType,

  DisputeType,

  TaxID,

  Mode,

  AssignedTo,

  ResolvedBy,

  DATE(DateResolved) AS DateResolved,

  DATE(DateCreated) AS DateCreated,

  ProductLine,

  CreditDisputeType,

  IF(Mode='e-Oscar',0,1) AS [Not_e-Oscar_Dum],

  DATE(DateCreated + 30) AS [Due Date],

  MONTH(DATE(DateCreated)) AS [Month Created],

  YEAR(DATE(DateCreated)) AS [Year Created],

  'Q' & CEIL(MONTH(DateCreated) / 3) AS Quarter

FROM

[$(vPathname)CET_$(vCETDataDate).xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE DateCreated >= $(vCETLastExecTime) AND DateCreated < $(vExecTime);

IF NOT ISNULL(QVDCREATETIME('$(vQVDPath)CETData.qvd')) THEN

  CONCATENATE(CET)

  LOAD Pkey,

  State,

  AccountType,

  DisputeType,

  TaxID,

  Mode,

  AssignedTo,

  ResolvedBy,

  DateResolved,

  DateCreated,

  ProductLine,

  CreditDisputeType,

  IF(Mode='e-Oscar',0,1) AS [Not_e-Oscar_Dum],

  DATE(DateCreated + 30) AS [Due Date],

  MONTH(DATE(DateCreated)) as [Month Created],

  YEAR(DATE(DateCreated)) as [Year Created],

  'Q' & CEIL(MONTH(DateCreated) / 3) AS Quarter

  FROM

  $(vQVDPath)CETData.qvd (QVD)

  WHERE NOT(EXISTS (Pkey));

ENDIF;

Left Join (CET)

LOAD MAX(DateCreated) as MaxDateCreated

Resident CET;

FinalCET:

LOAD *,

  IF(DateResolved='Null',IF(MaxDateCreated-DateCreated>30,'Outstanding >30', 'Outstanding <30'),DateResolved - DateCreated) AS ResponseTime

Resident CET;

DROP Table CET;

IF NoOfRows('FinalCET') > 0 THEN

STORE FinalCET INTO $(vQVDPath)CETData.qvd (QVD) ;

//DROP TABLE CET ;

ENDIF;

[lucasdavis500]

This shouldn't obstruct the incremental load/update feature of this data, correct?

[lucasdavis500]

This seems to be working, but my values that were imported as 'empty' are not calculating to 'Outstanding blah' they just resolve to '-'. Any idea on how to solve this?

[sunny_talwar]

Can you check if MaxCreatedDate is showing any value or if that is also null ('-')?

[lucasdavis500]

I used the ALT function to turn my '-' or true null into a string 'Null' and then based my nested IF off of that... seems to be working.

alt(DateResolved,'Null') as ResolutionDate

FinalCET:
LOAD *,
IF(ResolutionDate='Null',IF(MaxDateCreated-DateCreated>30, 'Outstanding >30', 'Outstanding <30'),ResolutionDate-DateCreated) AS ResponseTime
RESIDENT CET;