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

[#1]

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-14	1
May-14	1
Jun-14	1
Jul-14	1
Aug-14	2
Sep-14	2
Oct-14	3
Nov-14	3
Dec-14	3
Jan-15	4
Feb-15	4
Mar-15	4
Apr-15	5
May-15	5

[Chanty4u]

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

[#4]

I use personal edition.

Can you paste the script here, pls

[#5]

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],

[#7]

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?

[#8]

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 · Accepted solution]

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:

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