Convert Number to date format

[upaliwije]

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

[Yousef_Amarneh]

use date(Max_Act_Paid_Date) to convert to date a

[tresesco]

Date(YourField, [Format])

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

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

[its_anandrjs]

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' )

[MK_QSL]

use below in your script instead of Max_Act_Paid_Date

Date(Max_Act_Paid_Date) as Max_Act_Paid_Date

[upaliwije]

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

[tresesco]

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);

[SunilChauhan · Accepted solution]

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;

[upaliwije]

Thanks all