Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Nested If Functions

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

1 Solution

Accepted Solutions
Highlighted

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;

View solution in original post

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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.


talk is cheap, supply exceeds demand
Highlighted

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?

Highlighted

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;

View solution in original post

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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?

Highlighted

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

Highlighted
Creator III
Creator III

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;