Creating new variables using If/Then

[lucasdavis500]

I have successfully created my incremental load script for my excel files. Next I have to create a series of dummies, etc that are based off of the data I currently have. My question is, what is this syntax, and will this cause errors in the rest of my code because the variable(s) I'm trying to create are not in the native excel/csv import:

My IF Statement is where I want to create a variable. I want to create 'Not_E-Oscar_Dum' based on the value of the variable Mode. Qlikview won't take my current syntax. What is wrong with it?!?!?!

Also, I'm going to have quite a few of these dummies created. When I create 'Not_E-Oscar_Dum' in the first LOAD statement I should just be able to then add 'Not_E-Oscar_Dum' to my 2nd load statement, correct?

//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', [Not_E-Oscar_Dum]=0, [Not_E-Oscar_Dum]=1)

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

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 ;

[sunny_talwar · Accepted solution]

Is this what you are trying to do?

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]

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]

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 ;