topic Re: Date in QlikView

Date

smilingjohn — Fri, 26 Feb 2016 03:51:09 GMT

HI All ,

How to convert dte into age ,

Iam attaching a sample data of xl and QVW file , i want to create one more field by name AGE using the dat field .Can someone help me please

Thanks in Advance....

Re: Date

jagan — Fri, 26 Feb 2016 04:26:24 GMT

Hi,

Try like this

Age:

LOAD AGE,

Age(Now(), TimeStamp(Timestamp#(AGE, 'M/D/YYYY hh:mm:ssTT'))) AS Age_Calc,

TimeStamp(Timestamp#(AGE, 'M/D/YYYY hh:mm:ssTT')) AS Date

FROM

[Age.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

Re: Date

senpradip007 — Fri, 26 Feb 2016 04:37:25 GMT

Have a look at this. May be it will help.

Re: Date

tamilarasu — Fri, 26 Feb 2016 04:39:18 GMT

Hi,

Check the attachment.

Re: Date

bohravanraj — Fri, 26 Feb 2016 04:47:12 GMT

Timestamp(Timestamp#('10/12/1929 12:00:00AM','M/D/YYYY hh:mm:ssTT'),'M/D/YYYY hh:mm:ssTT') Try Above Code

Re: Date

PradeepReddy — Fri, 26 Feb 2016 04:59:46 GMT

try some thing like this...

Age(Date(Today()), Date(Date#(AGE,'M/D/YYYY hh:mm:ssTT'))) as Age_Yrs

Re: Date

smilingjohn — Fri, 26 Feb 2016 05:10:59 GMT

HI Tamil Nagraj

Its not working ,

Please find the attachment of the real time data , i tried to use the logic but its not working

Re: Date

smilingjohn — Fri, 26 Feb 2016 05:11:50 GMT

HI Jagan

Its not working ,

Please find the attachment of the real time data , i tried to use the logic but its not working.

Re: Date

engishfaque — Fri, 26 Feb 2016 05:26:15 GMT

Dear John,

Please use this one,

Table1:

LOAD

Age(Date(Today(), 'DD/MM/YYYY'), Date(Date#(AGE,'M/D/YYYY hh:mm:ssTT'), 'DD/MM/YYYY')) as AGE

FROM

Age.xlsx

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed

Re: Date

jagan — Fri, 26 Feb 2016 05:29:19 GMT

Hi,

Try this script;

LOAD ACCNUMBER,

BATCODE,

BIRTHDATEODBC,

COLLDATEODBC,

Age(Now(), TimeStamp(Timestamp#(COLLDATEODBC, Date(Date#(SubField(COLLDATEODBC, ' '), 'M/D/YYYY'))))) AS Age,

COLLTIMEODBC,

DIREXAM,

DRUGCODE,

DRUGNAME,

PTNAME,

DRUGRESULT,

FINALDATEODBC,

LOCNAME,

LOCTYPE,

MO_GROUPCODE,

ORDERPHYS,

ORGANISMCODE,

ORGCODE,

ORGDUPFLAG

FROM

(ooxml, embedded labels, table is Data);

Regards,

jagan.

Re: Date

tamilarasu — Fri, 26 Feb 2016 05:35:20 GMT

John,

Simply try,

Age(Date(Today()),Date(Floor(COLLDATEODBC))) as Age

LOAD ACCNUMBER,
     BATCODE,
     BIRTHDATEODBC,
     COLLDATEODBC,
     Age(Date(Today()),Date(Floor(COLLDATEODBC))) AS Age,
     COLLTIMEODBC,
     DIREXAM,
     DRUGCODE,
     DRUGNAME,
     PTNAME,
     DRUGRESULT,
     FINALDATEODBC,
     LOCNAME,
     LOCTYPE,
     MO_GROUPCODE,
     ORDERPHYS,
     ORGANISMCODE,
     ORGCODE,
     ORGDUPFLAG    
FROM

(ooxml, embedded labels, table is Data);

Re: Date

senpradip007 — Fri, 26 Feb 2016 05:35:32 GMT

Try like

Age(Today(), TimeStamp(Timestamp#(COLLDATEODBC, 'M/D/YYYY hh:mm:ssTT'))) AS Age,

Re: Date

tresB — Fri, 26 Feb 2016 05:38:20 GMT

It is working. Only thing is - you have to understand is Age() - Returns the age at the time of timestamp (in completed years). That means if not a complete year it would return 0 and that is the case in your sample qvw. If you want your age calculation to include month, day difference - age() would not help, you might try with interval() instead.

One more thing - your expression could be optimized a little by removing the unnecessary timestamp(), like:

Age(Now(), TimeStamp(Timestamp#(COLLDATEODBC, 'M/D/YYYY hh:mm:ssTT')))

Or, even usign parsing function(since, it seems your source data is a proper numeric date field)

Age(Now(), COLLDATEODBC) as Age

Re: Date

Fri, 26 Feb 2016 05:44:13 GMT

Please try this

Age:

LOAD AGE,

subfield(AGE,' ',1) as Date,

SubField(AGE,' ',2) as Time

FROM

(ooxml, embedded labels, table is Sheet1);

Data:

load

AGE(today(),Date) as Totalage

resident Age;

DROP Table Age;

Re: Date

smilingjohn — Fri, 26 Feb 2016 05:45:40 GMT

HI Ishfaque,

PLease find the attachment its not working

Re: Date

tresB — Fri, 26 Feb 2016 05:48:22 GMT

May be like Stefan suggested here: Age in Year Month Day Format

Re: Date

smilingjohn — Fri, 26 Feb 2016 05:48:25 GMT

Hi jagan after following your steps i loaded but its showing 0 in age plz find he snapshot

Re: Date

smilingjohn — Fri, 26 Feb 2016 05:50:32 GMT

hi Nagraj

Same again every where its shoing 0 only as shown in the screen shot

Re: Date

jagan — Fri, 26 Feb 2016 05:50:43 GMT

Hi,

Try this

LOAD ACCNUMBER,

BATCODE,

BIRTHDATEODBC,

COLLDATEODBC,

Age(Now(), Date(Date#(SubField(COLLDATEODBC, ' '), 'M/D/YYYY'))) AS Age,

COLLTIMEODBC,

DIREXAM,

DRUGCODE,

DRUGNAME,

PTNAME,

DRUGRESULT,

FINALDATEODBC,

LOCNAME,

LOCTYPE,

MO_GROUPCODE,

ORDERPHYS,

ORGANISMCODE,

ORGCODE,

ORGDUPFLAG

FROM

(ooxml, embedded labels, table is Data);

Re: Date

tamilarasu — Fri, 26 Feb 2016 05:56:17 GMT

Age function shows only years. Actually, the year is 0 in your case, since your date values are 8/1/2015 12:00:00AM and 8/2/2015 12:00:00AM (Not yet completed one year). Could you load full data and check the result.!