Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Convert Number to date format

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

1 Solution

Accepted Solutions
SunilChauhan
Champion II
Champion II

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;

Sunil Chauhan

View solution in original post

8 Replies
Yousef_Amarneh
Partner - Creator III
Partner - Creator III

use date(Max_Act_Paid_Date) to convert to date a

Yousef Amarneh
tresesco
MVP
MVP

Date(YourField, [Format])

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

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

its_anandrjs
Champion III
Champion III

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
MVP
MVP

use below in your script instead of Max_Act_Paid_Date

Date(Max_Act_Paid_Date) as Max_Act_Paid_Date

upaliwije
Creator II
Creator II
Author

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
MVP
MVP

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
Champion II
Champion II

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;

Sunil Chauhan
upaliwije
Creator II
Creator II
Author

Thanks all