topic Re: Convert Number to date format in QlikView

Convert Number to date format

upaliwije — Thu, 31 Jul 2014 10:37:29 GMT

Hi,

When I create QVD file I have a date field as follows in my sql

max(a.cre_acc_paid_date)Max_Act_Paid_Date,

Resulting Date format is as follows

41327

41318

41320 etc

When I load data from QVD file to my QV data model I want the above number format to convert to a date format which will ultimately be linked to a master calender,

Pls educate me to convert the number format in to date format in the script or in Master Calender

Max_Act_Paid_Date

Re: Convert Number to date format

Yousef_Amarneh — Thu, 31 Jul 2014 10:39:51 GMT

use date(Max_Act_Paid_Date) to convert to date a

Re: Convert Number to date format

tresB — Thu, 31 Jul 2014 10:39:56 GMT

Date(YourField, [Format])

Date(YourField, 'DD-MMM-YYYY') or,

Date(YourField, 'MM-DD-YYYY') or, ....

Re: Convert Number to date format

its_anandrjs — Thu, 31 Jul 2014 10:41:09 GMT

When reading from the QVD into Qlikview then convert it into date format by

Date( Max_Act_Paid_Date )

Or specify the DATE format that you use like

Date( Max_Act_Paid_Date,'DD/MM/YYYY' )

Re: Convert Number to date format

MK_QSL — Thu, 31 Jul 2014 10:46:18 GMT

use below in your script instead of Max_Act_Paid_Date

Date(Max_Act_Paid_Date) as Max_Act_Paid_Date

Re: Convert Number to date format

upaliwije — Thu, 31 Jul 2014 10:53:05 GMT

Thanks all

It is working but when I connect it to following master calender the date does not get linked

LOAD

Day(MAX_ACT_PAID_DATE) AS PAID_DAY,

WeekDay(MAX_ACT_PAID_DATE) AS P_WEEK_DAY,

Date(MAX_ACT_PAID_DATE) AS PAID_DATE,

Week(MAX_ACT_PAID_DATE) AS PAID_WEEK,

Year(MAX_ACT_PAID_DATE) AS PAID_YEAR,

Month(MAX_ACT_PAID_DATE) As PAID_MONTH,

Month(MAX_ACT_PAID_DATE) &'-'& week(MAX_ACT_PAID_DATE) As P_Month_Week;

Load Date(PAID_MinDate + IterNo() -1 ) AS MAX_ACT_PAID_DATE While (PAID_MinDate + IterNo() - 1) <= Num(PAID_MaxDate);

Load

Min(MAX_ACT_PAID_DATE) AS PAID_MinDate,

Max(MAX_ACT_PAID_DATE) AS PAID_MaxDate

RESIDENT Claim;

Pls help

Re: Convert Number to date format

tresB — Thu, 31 Jul 2014 10:58:59 GMT

try like:

Temp:

Load

Min(MAX_ACT_PAID_DATE) AS PAID_MinDate,

Max(MAX_ACT_PAID_DATE) AS PAID_MaxDate

RESIDENT Claim;

Let vMindate=Num(Peek( 'PAID_MinDate'));

Let vMaxdate=Num(Peek( 'PAID_MaxDate'));

Drop Table Temp;

LOAD

.....

Load Date($(vMindate)+ IterNo() -1 ) AS MAX_ACT_PAID_DATE While ($(vMindate)+ IterNo() - 1) <= $(vMaxdate);

Re: Convert Number to date format

SunilChauhan — Thu, 31 Jul 2014 11:00:57 GMT

LOAD

Day(MAX_ACT_PAID_DATE) AS PAID_DAY,

WeekDay(MAX_ACT_PAID_DATE) AS P_WEEK_DAY,

Date(MAX_ACT_PAID_DATE) AS PAID_DATE,

Date(MAX_ACT_PAID_DATE) as Max_Act_Paid_Date,

Week(MAX_ACT_PAID_DATE) AS PAID_WEEK,

Year(MAX_ACT_PAID_DATE) AS PAID_YEAR,

Month(MAX_ACT_PAID_DATE) As PAID_MONTH,

Month(MAX_ACT_PAID_DATE) &'-'& week(MAX_ACT_PAID_DATE) As P_Month_Week;

Load Date(PAID_MinDate + IterNo() -1 ) AS MAX_ACT_PAID_DATE While (PAID_MinDate + IterNo() - 1) <= Num(PAID_MaxDate);

Load

Min(MAX_ACT_PAID_DATE) AS PAID_MinDate,

Max(MAX_ACT_PAID_DATE) AS PAID_MaxDate

RESIDENT Claim;

Re: Convert Number to date format

upaliwije — Thu, 31 Jul 2014 11:37:31 GMT

Thanks all