Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi to all,
In my file having an two inline tables. These two tables having same fields i.e; DateSelection.
Here first table having Dates from 29-05-2011 10:59:59 to 31-05-2011 12:59:59.
And Second table contains Dates from 01-06-2011 10:59:59 to 16-06-2011 10:09:59.
But my requirement is when i select a "DateSelection" in listbox , it should get the dates from 29-05-2011 05:05:55 to 16-06-2011 06:06:55 ( TOTAL 19 DATES SHOULD VISIBLE IN A LISTBOX).
How can i work on this.
Below Script is what i used in Edit-Script.
FirstTable:
LOAD
DatesField,
Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
SubField(DatesField, ' ', 1) AS CapDate
Inline
[DatesField
29-05-2011 10:59:59
30-05-2011 11:59:59
31-05-2011 12:59:59];
SecondTable:
LOAD
DatesField,
Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
SubField(DatesField, ' ', 1) AS CapDate
INLINE [
DatesField
01-06-2011 10:59:59
02-06-2011 11:59:59
03-06-2011 12:59:59
04-06-2011 1:40:59
04-06-2011 1:50:59
05-06-2011 2:59:59
06-06-2011 3:59:59
07-06-2011 4:59:59
08-06-2011 5:59:59
09-06-2011 6:59:59
10-06-2011 2:59:59
12-06-2011 7:39:59
13-06-2011 8:29:59
14-06-2011 9:19:59
15-06-2011 10:09:59
16-06-2011 10:02:59
16-06-2011 10:09:59 ];
I hope you understand what i am trying to acheive.
Regards
Venkat
HI,
Try this code.
Sample:
LOAD * Inline
[DatesField
29-05-2011 10:59:59
30-05-2011 11:59:59
31-05-2011 12:59:59];
Concatenate
LOAD *
INLINE [
DatesField
01-06-2011 10:59:59
02-06-2011 11:59:59
03-06-2011 12:59:59
04-06-2011 1:40:59
04-06-2011 1:50:59
05-06-2011 2:59:59
06-06-2011 3:59:59
07-06-2011 4:59:59
08-06-2011 5:59:59
09-06-2011 6:59:59
10-06-2011 2:59:59
12-06-2011 7:39:59
13-06-2011 8:29:59
14-06-2011 9:19:59
15-06-2011 10:09:59
16-06-2011 10:02:59
16-06-2011 10:09:59 ];
Sample1:
Load
Date(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
SubField(DatesField, ' ', 1) AS CapDate
Resident Sample;
Drop table Sample;
Regards,
Kaushik Solanki
Hey Venkat,
Not sure if you lost something in Copy/Paste but it looks close to correct:
FirstTable:
LOAD
DatesField,
Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
SubField(DatesField, ' ', 1) AS CapDate;
Load * Inline
[DatesField
29-05-2011 10:59:59
30-05-2011 11:59:59
31-05-2011 12:59:59];
SecondTable:
LOAD
DatesField,
Date#(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
SubField(DatesField, ' ', 1) AS CapDate;
Load * INLINE [
DatesField
01-06-2011 10:59:59
02-06-2011 11:59:59
03-06-2011 12:59:59
04-06-2011 1:40:59
04-06-2011 1:50:59
05-06-2011 2:59:59
06-06-2011 3:59:59
07-06-2011 4:59:59
08-06-2011 5:59:59
09-06-2011 6:59:59
10-06-2011 2:59:59
12-06-2011 7:39:59
13-06-2011 8:29:59
14-06-2011 9:19:59
15-06-2011 10:09:59
16-06-2011 10:02:59
16-06-2011 10:09:59 ];
Assuming the dates load correctly like this you should be good to go with the two tables being automatically concatenated into one table.
HI,
Try this code.
Sample:
LOAD * Inline
[DatesField
29-05-2011 10:59:59
30-05-2011 11:59:59
31-05-2011 12:59:59];
Concatenate
LOAD *
INLINE [
DatesField
01-06-2011 10:59:59
02-06-2011 11:59:59
03-06-2011 12:59:59
04-06-2011 1:40:59
04-06-2011 1:50:59
05-06-2011 2:59:59
06-06-2011 3:59:59
07-06-2011 4:59:59
08-06-2011 5:59:59
09-06-2011 6:59:59
10-06-2011 2:59:59
12-06-2011 7:39:59
13-06-2011 8:29:59
14-06-2011 9:19:59
15-06-2011 10:09:59
16-06-2011 10:02:59
16-06-2011 10:09:59 ];
Sample1:
Load
Date(DatesField, 'MM-DD-YYYY') AS REPEAT_DATE,
SubField(DatesField, ' ', 1) AS CapDate
Resident Sample;
Drop table Sample;
Regards,
Kaushik Solanki
Hi Kaushik,
what you given code is working for both INLINE tables.
But when i am trying to do by field from QVD and INLINE , it is not showing the Distinct Dates.
Pls see the below code:
Sample:
LOAD
CaptureDateTime
FROM
(qvd);
Concatenate
LOAD * INLINE
[CaptureDateTime
06-05-2011 3:59:59
07-05-2011 4:59:59
08-05-2011 5:59:59
09-05-2011 6:59:59
10-05-2011 2:59:59
12-05-2011 7:39:59
13-05-2011 8:29:59
14-05-2011 9:19:59
15-05-2011 10:09:59
16-05-2011 10:02:59
16-05-2011 10:09:59 ];
Sample2:
LOAD
Date(CaptureDateTime, 'MM-DD-YYYY') as RepeatCaptureDate,
SubField(CaptureDateTime, ' ', 1) as DistinctCaptureDate
Resident Sample;
DROP Table Sample;
The Result for DistinctCaptureDate is shown as below: i.e; from QVD it is not getting the DISTINCT dates.
Regards
Venkat
Hello Venkat,
The reason is that they are not actually distinct, since the Date() function you are using represents the date but keeps the subjacent numeric value for each record, so the dates contain times and thus are different. Rather, making a new field deleting the time part of the timestamp will do:
Sample:
LOAD
CaptureDateTime AS OriginalDateTimeField,
Date(Floor(CaptureDateTime), 'DD-MM-YYYY') AS CaptureDateTime
FROM
(qvd);
You keep the original field should you need to use it later, but creating a listbox with CaptureDateTime will only show the date parts, so when you select "15-05-2011" all values in the original field with this date will be selected.
Hope that help.s
BI Consultant
Hi, No all is ok, pls see image adjust, Good luck, Luis
Important: I use date# for convert string to format date field