topic Re: Date Field in QlikView

Date Field

upaliwije — Sat, 02 May 2020 10:39:49 GMT

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.

Re: Date Field

agni_gold — Sun, 03 May 2020 10:37:34 GMT

can you please share your script.

Re: Date Field

upaliwije — Sun, 03 May 2020 10:54:59 GMT

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;

Re: Date Field

agni_gold — Sun, 03 May 2020 11:30:13 GMT

can you check your

NUM(RISK_DATE)

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

Re: Date Field

HirisH_V7 — Sun, 03 May 2020 13:38:16 GMT

use this:

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

Re: Date Field

upaliwije — Wed, 06 May 2020 03:19:36 GMT

Not working please

Re: Date Field

upaliwije — Wed, 06 May 2020 03:20:18 GMT

value is null please

Re: Date Field

HirisH_V7 — Thu, 07 May 2020 16:46:08 GMT

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