Date Field

[upaliwije]

Hi friends,

I have a little problem with date filed which I am uanle to slove. In my attached QV document I am trying to claculate No of Days and the Age . Both fields return null values Kindly have a look ast my doc and hel me to solve the issue.

[agni_gold]

can you please share your script.

[upaliwije]

Here is my script

Let vDate=Num(Date(Makedate(2020,04,22),'DD-MMM-YYYY'));
let vRmonth=month($(vDate));
let vRyear=year($(vDate));

DEB:

LOAD*,
//$(vDate)- num(date(RISK_DATE,'DD-MMM-YYYY')) AS DAYS;
Num(Date#(RISK_DATE,'DD-MMM-YYYY'))AS DAY1,
$(vDate)- NUM(RISK_DATE)AS DAYS;
LOAD
BCOD,
DEBIT_NOTE,
date(date#(RISK_DATE,'DD-MMM-YYYY')) AS RISK_DATE,
BALANCE,
'MO' AS CAT
FROM
D:\Qlikview\Debtors\IT\April22_Motor.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHERE DEBIT_NOTE='CO2320D0013127'
;

Concatenate(DEB)

LOAD

BCOD,
DEBIT_NOTE,
date(date#(RISK_DATE,'DD-MMM-YYYY')) AS RISK_DATE,
FLOOR(RISK_DATE)AS DAY1,
BALANCE,
'NM' AS CAT
FROM
D:\Qlikview\Debtors\IT\April22_NonMotor.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHERE DEBIT_NOTE='CO2320D0013127'
;

DEB1:
LOAD*,
IF(DAYS<=30, dual('0-30',1),
IF(DAYS>=31 and DAYS<=60, dual('31-60',2),
IF(DAYS>=61 and DAYS<=90, dual('61-90',3),
IF(DAYS>=91 and DAYS<=105, dual('91-105',4),
IF(DAYS>105, dual('Over105',9),
)))))as AGE

Resident DEB;

DROP Table DEB;

[agni_gold]

can you check your 

NUM(RISK_DATE)

is making any number or not , and also check date variable contains what value 

[HirisH_V7]

use this:

$(vDate)- Num(Num(RISK_DATE,'M/D/YYYYY'))AS DAYS;

[upaliwije]

Not working please

[upaliwije]

value is null please

[HirisH_V7 · Accepted solution]

$(vDate)- Num(Num#(RISK_DATE,'M/D/YYYYY'))AS DAYS;