Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
$(vDate)- Num(Num#(RISK_DATE,'M/D/YYYYY'))AS DAYS;
can you please share your script.
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;
can you check your
NUM(RISK_DATE)
is making any number or not , and also check date variable contains what value
use this:
$(vDate)- Num(Num(RISK_DATE,'M/D/YYYYY'))AS DAYS;
Not working please
value is null please
$(vDate)- Num(Num#(RISK_DATE,'M/D/YYYYY'))AS DAYS;