How to get data by selecting date?

[#1]

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.

[#2]

Can anyone  suggest me?please

[Gysbert_Wassenaar]

Use the Intervalmatch function to match the interval between Initial and Final with your Calendar table. See this blog post: IntervalMatch

[#4]

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;

[#5]

I tried in many ways and nothing seems to be working. any suggestions?

[Gysbert_Wassenaar]

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.

[#7]

I tried it no data is showing. Any help please?

[simenkg · Accepted solution]

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.

[jonathandienst]

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

[#10]

working . Thanks.