Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Map a date (MMM-YY) to an index number

HI All,

I would like to map the date to an index. I have tried using load * inline but it was not successful and blank appeared in the index column.

LOAD * INLINE [
Date, index
Apr-14, 1
May-14, 1
Jun-14, 1
Jul-14, 1
Aug-14, 2
Sep-14, 2
10-14, 3
Nov-14, 3
Dec-14, 3
Jan-15, 4
Feb-15, 4
Mar-15, 4
Apr-15, 5
May-15, 5
]
;

this is the end result table.

   

Date Index
Apr-141
May-141
Jun-141
Jul-141
Aug-142
Sep-142
Oct-143
Nov-143
Dec-143
Jan-154
Feb-154
Mar-154
Apr-155
May-155
1 Solution

Accepted Solutions
MarcoWedel

Hi,

with your DateFormat  = 'MMM-YY' you were just formatting dates as months, so your inline loaded months did not match. You could instead try with:

QlikCommunity_Thread_210630_Pic1.JPG

QlikCommunity_Thread_210630_Pic2.JPG

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD-MM-YY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

table1:

LOAD Date(MonthStart(Date#(ScreeningMonth, 'MMM-YY')), 'MMM-YY') as ScreeningMonth,

ScreeningQtr;

LOAD * Inline [

    ScreeningMonth, ScreeningQtr

    Apr-15, Q1

    May-15, Q1

    Jun-15, Q1

    Jul-15, Q2

    Aug-15, Q2

    Sep-15, Q2

    Oct-15, Q3

    Nov-15, Q3

    Dec-15, Q3

];

table2:

LOAD [S/N],

    Customer,

    ScreeningDate,

    Date(MonthStart(ScreeningDate), 'MMM-YY') as ScreeningMonth

FROM [https://community.qlik.com/servlet/JiveServlet/download/1005950-218396/Customer_date.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

View solution in original post

13 Replies
Chanty4u
MVP
MVP

chk dis

sunny_talwar

Can you share the complete script that you are using? I don't see a reason why this wouldn't work

Not applicable
Author

I use personal edition.

Can you paste the script here, pls

Not applicable
Author

Hi,

The script as follows. Seek your advise.

*LOAD * INLINE [
ScreeningDate, ScreeningQtr
Apr-14, 1
May-14, 1
Jun-14, 1
Jul-14, 1
Aug-14, 2
Sep-14, 2
10-14, 3
Nov-14, 3
Dec-14, 3
Jan-15, 4
Feb-15, 4
Mar-15, 4
Apr-15, 5
May-15, 5
];


LOAD [S/N],
Company,
Date(ScreeningDate, 'MMM-YY') as [ScreeningDate],
[Name],

sunny_talwar

Try this:

LOAD Date(MonthStart(Date#(ScreeningDate, 'MMM-YY')), 'MMM-YY') as ScreeningDate,

          ScreeningQtr;

LOAD * INLINE [

ScreeningDate, ScreeningQtr

Apr-14, 1

May-14, 1

Jun-14, 1

Jul-14, 1

Aug-14, 2

Sep-14, 2

10-14, 3

Nov-14, 3

Dec-14, 3

Jan-15, 4

Feb-15, 4

Mar-15, 4

Apr-15, 5

May-15, 5

];

LOAD [S/N],

          Company,

          Date(MonthStart(Date#(ScreeningDate, 'MMM-YY')), 'MMM-YY') as ScreeningDate,

          [Name],

Not applicable
Author

HI,

Still not able to get it. now, I see blank in both screeningDate and ScreeningQtr.

I tried using this the following and was able to see the ScreeningDate but the ScreeningQtr is still blank.

Date(ScreeningDate, 'MMM-YY') as ScreeningDate,

Is there something wrong with the date format which I need to set?

Not applicable
Author

Hi,

I have attached the excel sheet and QVD and attached my script. I have changed the dateformat to MMM/YY and I am getting blank.

For your kind assistance, pls. let me have the updated scripts for the followings:

1. The inline mapping

2. finding the index difference in same/duplicate customer

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='MMM-YY';
SET TimestampFormat='DD-MM-YY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';



LOAD Date(MonthStart(Date#(ScreeningDate, 'MMM-YY')), 'MMM-YY') as ScreeningDate,
ScreeningQtr;

LOAD * INLINE [
ScreeningDate, ScreeningQtr
Apr-15, 1
May-15, 1
Jun-15, 1
Jul-15, 2
Aug-15, 2
Sep-15, 2
Oct-15, 3
Nov-15, 3
Dec-15, 3
]
;

LOAD [S/N],
Customer,
//ScreeningDate
   Date(MonthStart(Date#(ScreeningDate, 'MMM-YY')), 'MMM-YY') as ScreeningDate
FROM

(
ooxml, embedded labels, table is Sheet1);


MarcoWedel

Hi,

with your DateFormat  = 'MMM-YY' you were just formatting dates as months, so your inline loaded months did not match. You could instead try with:

QlikCommunity_Thread_210630_Pic1.JPG

QlikCommunity_Thread_210630_Pic2.JPG

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD-MM-YY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

table1:

LOAD Date(MonthStart(Date#(ScreeningMonth, 'MMM-YY')), 'MMM-YY') as ScreeningMonth,

ScreeningQtr;

LOAD * Inline [

    ScreeningMonth, ScreeningQtr

    Apr-15, Q1

    May-15, Q1

    Jun-15, Q1

    Jul-15, Q2

    Aug-15, Q2

    Sep-15, Q2

    Oct-15, Q3

    Nov-15, Q3

    Dec-15, Q3

];

table2:

LOAD [S/N],

    Customer,

    ScreeningDate,

    Date(MonthStart(ScreeningDate), 'MMM-YY') as ScreeningMonth

FROM [https://community.qlik.com/servlet/JiveServlet/download/1005950-218396/Customer_date.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

sunny_talwar

Try this:

LOAD Date(MonthStart(Date#(ScreeningDate, 'MMM-YY')), 'MMM-YY') as ScreeningDate,

          ScreeningQtr;

LOAD * INLINE [

    ScreeningDate, ScreeningQtr

    Apr-15, Q1

    May-15, Q1

    Jun-15, Q1

    Jul-15, Q2

    Aug-15, Q2

    Sep-15, Q2

    Oct-15, Q3

    Nov-15, Q3

    Dec-15, Q3

];

LOAD [S/N],

    Customer,

    //ScreeningDate

    Date(MonthStart(ScreeningDate), 'MMM-YY') as ScreeningDate

FROM

[Customer_date.xlsx]

(ooxml, embedded labels, table is Sheet1);