topic Expressions help in script editor in QlikView

Expressions help in script editor

shehovsovi — Tue, 28 May 2019 21:48:11 GMT

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

Re: Expressions help in script editor

Lisa_P — Wed, 29 May 2019 01:12:47 GMT

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,

Re: Expressions help in script editor

zhadrakas — Wed, 29 May 2019 06:51:44 GMT

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

Re: Expressions help in script editor

shehovsovi — Wed, 29 May 2019 14:53:40 GMT

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?

Re: Expressions help in script editor

shehovsovi — Wed, 29 May 2019 14:57:39 GMT

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?