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

[MarcoWedel]

you could also avoid your inline table like this:

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

table2:
LOAD [S/N],
    Customer,
    ScreeningDate,
    Date(MonthStart(ScreeningDate), 'MMM-YY') as ScreeningMonth,
    'Q'&Ceil(Mod(Month(ScreeningDate)+9,12)/3) as ScreeningQtr
FROM [https://community.qlik.com/servlet/JiveServlet/download/1005950-218396/Customer_date.xlsx] (ooxml, embedded labels, table is Sheet1)
Where [S/N];

hope this helps

regards

Marco

[#3]

Hi

Can I also know For question 2 finding the quarter difference in the same or duplicate customers?

[MarcoWedel]

Please close your thread by marking a correct answer if your initial question is answered and open another thread for independent questions.

Thanks

Regards

Marco

[#5]

It works.

Question 2 is related to this question 1.

will you all be able to advise?