Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

pls check the script

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

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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.


CaptureDist.PNG

Regards

Venkat

Miguel_Angel_Baeyens

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

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

llauses243
Creator III
Creator III

Hi, No all is ok, pls see image adjust, Good luck, Luis

Important:  I use date# for convert string to format date field