topic Re: pls check the script in QlikView

pls check the script

Tue, 28 Jun 2011 05:32:28 GMT

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

pls check the script

Anonymous — Tue, 28 Jun 2011 06:09:47 GMT

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 [

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

kaushiknsolanki — Tue, 28 Jun 2011 06:20:09 GMT

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

Re: pls check the script

Tue, 28 Jun 2011 09:38:49 GMT

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

Re: pls check the script

Miguel_Angel_Baeyens — Tue, 28 Jun 2011 09:52:57 GMT

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

Re: pls check the script

llauses243 — Fri, 15 Jul 2011 10:44:51 GMT

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

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