Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

pls check the script

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

5 Replies
jsn
Honored Contributor

pls check the script

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.

pls check the script

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

Not applicable

Re: pls check the script

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

Re: pls check the script

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
Contributor III

Re: pls check the script

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

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

Community Browser