Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
chk dis
Can you share the complete script that you are using? I don't see a reason why this wouldn't work
I use personal edition.
Can you paste the script here, pls
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],
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],
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?
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);
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
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);