topic Re: Nested If Functions in QlikView

Nested If Functions

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Nested If Functions

Gysbert_Wassenaar — Wed, 12 Oct 2016 16:45:17 GMT

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.

Re: Nested If Functions

sunny_talwar — Wed, 12 Oct 2016 16:45:18 GMT

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?

Re: Nested If Functions

sunny_talwar — Wed, 12 Oct 2016 18:44:24 GMT

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;

Re: Nested If Functions

lucasdavis500 — Wed, 12 Oct 2016 19:45:38 GMT

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

Re: Nested If Functions

lucasdavis500 — Wed, 12 Oct 2016 19:55:55 GMT

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?

Re: Nested If Functions

sunny_talwar — Wed, 12 Oct 2016 20:11:45 GMT

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

Re: Nested If Functions

lucasdavis500 — Wed, 12 Oct 2016 21:04:50 GMT

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;