Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have script as below
Sort:
MAPPING LOAD * INLINE [
Month, MonthSort
Jul, 1
Aug, 2
Sep, 3
Oct, 4
Nov, 5
Dec, 6
Jan, 7
Feb, 8
Mar, 9
Apr, 10
May, 11
Jun, 12
];
Calendar:
LOAD Date(Date,'DD/MM/YYYY') as Initial,
Date_TS,
Today,
Week,
Year,
Text(Month) as Month,
ApplyMap('Sort',Text(Month)) as MonthSort,
Day,
WeekDay,
Quarter,
MonthYear,
WeekYear,
CurYTDFlag,
LastYTDFlag,
FiscalYear,
FiscalYearStart,
FiscalWeek,
FiscalWeekSerial,
FiscalQuarter
FROM
[\Calendar.qvd]
(qvd);
CurrentFinancialWeek:
LOAD Year,Week,FiscalWeek,FiscalWeekSerial,FiscalYear,FiscalYearStart RESIDENT Calendar WHERE Week=(Week(Today())) and Year=Year(Today())
Order BY Year ASC;
LET curFiscalWeek=Peek('FiscalWeek',0,CurrentFinancialWeek);
LET curFiscalYear=Peek('FiscalYear',0,CurrentFinancialWeek);
LET curWeek=Peek('Week',0,CurrentFinancialWeek);
LET curYear=Peek('Year',0,CurrentFinancialWeek);
LET curFiscalWeekSeq=Peek('FiscalWeekSerial',0,CurrentFinancialWeek);
LET curFiscalYearStart=Peek('FiscalYearStart',0,CurrentFinancialWeek);
DROP TABLE CurrentFinancialWeek;
Customer:
LOAD [CustomerID],
Date(Initial,'DD/MM/YYYY') as Initial,
Date(Final, 'DD/MM/YYYY') as Final,
[Detailed],
StaffID
FROM
[..\Open\CusInfo.xlsx]
(ooxml, embedded labels, table is Sheet1);
when I made selections in list box Detailed data is showing in charts. But when I select Year/Month/Quarter the charts are showing as blank. Please can anyone suggest me how to set this.
List box have values CustomerPresence, CustomerAbsence, CustomerOutofRange
EDIT : Sample data: for eg: Initial and Final have dates as below
Initial Final
01/10/2014 08:45:19 01/10/2014 08:56:37
01/10/2014 08:46:24 01/10/2014 08:58:42
01/10/2014 08:46:25 01/10/2014 09:07:30
01/10/2014 08:46:32 01/10/2014 08:50:22
01/10/2014 08:49:28 01/10/2014 09:07:30
01/10/2014 08:50:08 01/10/2014 08:53:38
01/10/2014 08:51:57 01/10/2014 08:57:56
01/10/2014 08:52:47 01/10/2014 08:54:58
01/10/2014 08:53:34 01/10/2014 08:56:50
Thanks.
Customer:
LOAD [CustomerID],
Date(floor(Initial),'DD/MM/YYYY') as Initial,
Date(Final, 'DD/MM/YYYY') as Final,
[Detailed],
StaffID
FROM
[..\Open\CusInfo.xlsx]
(ooxml, embedded labels, table is Sheet1);
The problem is that you have a timestamp on your Initial Date. the Date() function only formats the field, it does not remove the timestamp. So even though they will show the same date 21/03/2014 will not match 21/03/2014 if the two dates are really 21/03/2014 00:00:00 and 21/03/2014 21:21:21
In order to fix this you have to use floor() on your keys to remove the timestamp.
Can anyone suggest me?please
Use the Intervalmatch function to match the interval between Initial and Final with your Calendar table. See this blog post: IntervalMatch
Thanks. when I am using interval match as below. synthetic keys are forming on Initial and Final. Is there anything wrong in below script?
Sort:
MAPPING LOAD * INLINE [
Month, MonthSort
Jul, 1
Aug, 2
Sep, 3
Oct, 4
Nov, 5
Dec, 6
Jan, 7
Feb, 8
Mar, 9
Apr, 10
May, 11
Jun, 12
];
Calendar:
LOAD Date(Date,'DD/MM/YYYY') as Date,
Date_TS,
Today,
Week,
Year,
Text(Month) as Month,
ApplyMap('Sort',Text(Month)) as MonthSort,
Day,
WeekDay,
Quarter,
MonthYear,
WeekYear,
CurYTDFlag,
LastYTDFlag,
FiscalYear,
FiscalYearStart,
FiscalWeek,
FiscalWeekSerial,
FiscalQuarter
FROM
[Calendar.qvd]
(qvd);
CurrentFinancialWeek:
LOAD Year,Week,FiscalWeek,FiscalWeekSerial,FiscalYear,FiscalYearStart RESIDENT Calendar WHERE Week=(Week(Today())) and Year=Year(Today())
Order BY Year ASC;
LET curFiscalWeek=Peek('FiscalWeek',0,CurrentFinancialWeek);
LET curFiscalYear=Peek('FiscalYear',0,CurrentFinancialWeek);
LET curWeek=Peek('Week',0,CurrentFinancialWeek);
LET curYear=Peek('Year',0,CurrentFinancialWeek);
LET curFiscalWeekSeq=Peek('FiscalWeekSerial',0,CurrentFinancialWeek);
LET curFiscalYearStart=Peek('FiscalYearStart',0,CurrentFinancialWeek);
DROP TABLE CurrentFinancialWeek;
Customer:
Load * From
[..\Open\CusInfo.xlsx]
(ooxml, embedded labels, table is Sheet1);
IntervalMatch:
IntervalMatch (Date)
Load distinct Initial,Final resident Customer;
I tried in many ways and nothing seems to be working. any suggestions?
From the blog post:
Further, the data model contains a composite key (the FromDate and ToDate fields) which will manifest itself as a QlikView synthetic key. But have no fear. This synthetic key should be there; not only is it correct, but it is also optimal given the data model. You do not need to remove it.
I tried it no data is showing. Any help please?
Customer:
LOAD [CustomerID],
Date(floor(Initial),'DD/MM/YYYY') as Initial,
Date(Final, 'DD/MM/YYYY') as Final,
[Detailed],
StaffID
FROM
[..\Open\CusInfo.xlsx]
(ooxml, embedded labels, table is Sheet1);
The problem is that you have a timestamp on your Initial Date. the Date() function only formats the field, it does not remove the timestamp. So even though they will show the same date 21/03/2014 will not match 21/03/2014 if the two dates are really 21/03/2014 00:00:00 and 21/03/2014 21:21:21
In order to fix this you have to use floor() on your keys to remove the timestamp.
Hi
The problem is that your field Initial in Customer has a time component, and I suspect that the calendar does not. Use the Date() formatting function does not remove the time component - so your calendar is never matching your data in Customer.
Load Customer with this script:
Customer:
LOAD [CustomerID],
Date(Floor(Initial),'DD/MM/YYYY' as Initial,
Date(Floor(Final), 'DD/MM/YYYY' as Final,
[Detailed],
StaffID
FROM
[..\Open\CusInfo.xlsx]
(ooxml, embedded labels, table is Sheet1);
Interval matching will be useful for identifying the correct value of Detailed for a given date range.
HTH
Jonathan
working . Thanks.