Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shehovsovi
Contributor
Contributor

Expressions help in script editor

Hello,

I'm new to Qlikview. My question is about data loading and expression building:

I'm getting an errror that I'm missing right parenthesis but i don't think it is it. cant find what is wrong

I'm trying to subtract 1 month from a character based date, i.e. 201904 minus 1 month = 3/1/2019 (Control Date)

this is what i'm doing: wondering if there is a better way? are long if statements allowed in the editor? what is wrong with my statement?

SQL SELECT
T.CCYYMM AS LOAN_CCYYMM,
if(right(LOAN_CCYYMM,2)='01',date(left(LOAN_CCYYMM,4)-1,12,1),date(left(LOAN_CCYYMM,4),right(LOAN_CCYYMM,2)-1,1)) as CONTROL_DATE,

 

Thank you

4 Replies
Lisa_P
Employee
Employee

Trying to break down your logic
if(
right(LOAN_CCYYMM,2)='01', // then ..
date(left(LOAN_CCYYMM,4)-1,12,1), // else .. ???? The date function only has 2 parameters date(number[, format])
date(left(LOAN_CCYYMM,4),
right(LOAN_CCYYMM,2)-1,1)) as CONTROL_DATE,
zhadrakas
Specialist II
Specialist II

you are using QLIK Syntax in SQL. Try like this:

Load *,
if(right(LOAN_CCYYMM,2)='01',date(left(LOAN_CCYYMM,4)-1,12,1),date(left(LOAN_CCYYMM,4),right(LOAN_CCYYMM,2)-1,1)) as CONTROL_DATE
;
SQL SELECT
T.CCYYMM AS LOAN_CCYYMM,
..
;
shehovsovi
Contributor
Contributor
Author

I'm using LOAN_CCYYMM in my calculation. I get this field from a database through SQL loading. Shouldn't I first load the field before I can use it in calc in order of operations?

shehovsovi
Contributor
Contributor
Author

i had a formula in Excel which i'm trying to replicate in Qlikview. 

I'm trying to calculate number of days delinquent between two dates. First I'm creating a control date and then will be using control date to subtract another date. So result would be something like 30,60,90 days
LOAN_CCYYMM is a text field ie 201904. I'm trying to create a new field from that by subtracting 1 month from 04 and then converting the result to date: i.e 3/1/2019

Is there a better way to do what i'm trying to do?