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

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

Chanty4u — Wed, 23 Mar 2016 11:34:47 GMT

chk dis

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

sunny_talwar — Wed, 23 Mar 2016 11:38:07 GMT

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

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

Wed, 23 Mar 2016 13:44:03 GMT

I use personal edition.

Can you paste the script here, pls

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

Thu, 24 Mar 2016 02:20:43 GMT

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

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

sunny_talwar — Thu, 24 Mar 2016 10:13:31 GMT

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

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

Sat, 26 Mar 2016 13:29:29 GMT

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?

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

Sat, 26 Mar 2016 14:23:26 GMT

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

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

MarcoWedel — Sun, 27 Mar 2016 01:07:25 GMT

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

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

sunny_talwar — Sun, 27 Mar 2016 01:08:47 GMT

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

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

MarcoWedel — Sun, 27 Mar 2016 01:17:32 GMT

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

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

Sun, 27 Mar 2016 02:52:17 GMT

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

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

MarcoWedel — Sun, 27 Mar 2016 09:39:21 GMT

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

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

Mon, 28 Mar 2016 01:35:22 GMT

It works.

Question 2 is related to this question 1.

will you all be able to advise?