Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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;
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.
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?
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;
This shouldn't obstruct the incremental load/update feature of this data, correct?
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?
Can you check if MaxCreatedDate is showing any value or if that is also null ('-')?
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;