topic Distinct data in listbox in QlikView

Distinct data in listbox

Wed, 11 May 2011 12:19:12 GMT

i have Capturedatetime feild having one day data with diffrent times.

then i need to display date only in one listbox and display distinct times in other list box

in the date list box i need to show the distinct date...but i get all dates repeated but time showing is fine in other list box

here how to show the distinct date in firs list box..

Is it possible.??

If possible ,

plz help me on this....

Many Thanks

Ravi

Distinct data in listbox

Wed, 11 May 2011 12:49:02 GMT

Hi,

Use this function to return just date

Subfiled()

Ex.:

Test:

LOAD

Date(vDate, 'DD/MM/YYYY') AS REPEAT_DATE,

SubField(vDate, ' ', 1) AS ONLY_DATE,

INLINE [

vDate

11/05/2011 12:30:00

11/05/2011 12:40:00

11/05/2011 12:50:00 ];

Re: Distinct data in listbox

Wed, 11 May 2011 13:18:33 GMT

helo i need to maintain the relation ship b/w two lsit boxes...

is it possible with ur solution.

i mean when i am selecting the date in list box .default time would be selected in time list box

Thanks a lot for ur reply.

Distinct data in listbox

erichshiino — Wed, 11 May 2011 13:24:37 GMT

Hi,

If the code works for you,just need to add the original field:

LOAD

Date(vDate, 'DD/MM/YYYY') AS REPEAT_DATE,

SubField(vDate, ' ', 1) AS ONLY_DATE,

vDate

INLINE [

vDate

11/05/2011 12:30:00

11/05/2011 12:40:00

11/05/2011 12:50:00 ];

Distinct data in listbox

Wed, 11 May 2011 13:25:21 GMT

ListBoxes in QlikView display only distinct values.

You notice that your application dispays many times "17/04/2011". Why ?

Because "17/04/2011" is just the format you asked the value to be displayed.

In reality, your listbox wants to show you :

- "17/04/2001 18:00:00" (--> displayed as "17/04/2011")

- "17/04/2001 18:02:00" (--> displayed as "17/04/2011")

- "17/04/2001 18:04:00" (--> displayed as "17/04/2011")

- ...

What you want is to display only the "date" part.

Look at the function "daystart", this will convert all your dates into "17/04/2001 00:00:00" and only 1 will be displayed in your list.

Example:

with the following settings

and the following data

;

will display

Whereas

with the expression

will display

Re: Distinct data in listbox

Wed, 11 May 2011 13:42:50 GMT

Hello,

Once again thank you for your replies.

I also need like this,

If i am selecting date from the StartDateListbox Then only one default time(any one) should be displayed .But what happening here is all the times regarding that date are displaying.

Is it possible,

please help me in that issue...

Many Thanks

Ravi

Distinct data in listbox

erichshiino — Thu, 12 May 2011 04:20:59 GMT

All of them are displayed because all of them are related through the original timestamp.

Do you want to show all the possible times or only one for each startdate? or to have a list of only default times?

Re: Distinct data in listbox

Thu, 12 May 2011 04:55:43 GMT

Hello Erich,

I want to do like this.

i want to provide that user has an option to select startdate seperately and starttime seperately.but what happening here is when date is slected all the times regarding that are selecteed automatically .

I think This will makes sense....

Is it possible..?

If possible , Let me know...

Thanks,

Distinct data in listbox

Thu, 12 May 2011 08:14:06 GMT

In QlikView, if the values are "possible", it gives the same result as if the values are "selected".

If you really need to make a selection on the possible values, you can use a macro.

The only trigger you can use is "OnDeactivate" over the listbox and use the "Select Possible" action.

The user will have to make his selection then click on another objet.

Re: Distinct data in listbox

Fri, 13 May 2011 06:44:21 GMT

Hi Erich,

I want to show only one time for each startdate.

Is it possible?

If possible ,Let me know the solution.

Thanks...

Re: Distinct data in listbox

Fri, 13 May 2011 12:30:42 GMT

Hi,

This is possible just your model demonstrate this information.

If exists more the one time for each date this information don't show how you want.

Ex.:

Date Time

01/01/2011 13:50:00

01/01/2011 14:35:30

01/01/2011 18:00:00

02/01/2011 13:00:00

02/01/2011 15:30:00

02/01/2011 17:55:00

If in your model have one date for one time this is possible.

Date Time

01/01/2011 13:50:00

02/01/2011 14:35:30

03/01/2011 18:00:00

04/01/2011 13:00:00

05/01/2011 15:30:00

06/01/2011 17:55:00

Re: Distinct data in listbox

Fri, 13 May 2011 13:26:11 GMT

Hi ecorrea

thank you for your reply

in my model i had a single day with differnt times.

ex:

Date: Time:

01/01/2011 13:50:00

01/01/2011 14:35:30

01/01/2011 18:00:00

01/01/2011 13:00:00

01/01/2011 15:30:00

01/01/2011 17:55:00

But I have to provide for the user to select date and time seperately then what should i do?

Is there any other way to sove my issue?

If you had any suggestions for me , Let me know .

Thanks.

Re: Distinct data in listbox

Fri, 13 May 2011 13:53:56 GMT

If you add a listbox with the dimension "Date" and a 2nd listbox with the dimension "Time", the user will be able to select date and time separatly.

LOAD * INLINE [

Date, Time

01/01/2011, 13:50:00

01/01/2011, 14:35:30

01/01/2011, 18:00:00

01/01/2011, 13:00:00

01/01/2011, 15:30:00

01/01/2011, 17:55:00

];

Will give :

Date and time can be selected with 2 different lixtboxes.

Re: Distinct data in listbox

rbecher — Fri, 13 May 2011 14:01:32 GMT

You have to have two separate fields: Date and Time

Test:

LOAD

Date(Date#(vDate, 'DD/MM/YYYY hh:mm:ss')) AS Date,

Time(Date#(vDate, 'DD/MM/YYYY hh:mm:ss')) AS Time;

LOAD * INLINE [

vDate

11/05/2011 12:30:00

11/05/2011 12:40:00

11/05/2011 12:50:00 ];

Then make two listboxes.

- Ralf

Re: Distinct data in listbox

Fri, 13 May 2011 14:03:01 GMT

Hi nmartin

Thanks for your reply

Ofcourse it is possible in your example because those are two differant fields(date,time).

But, I have only one field (datetime) from that only i have to show seperately date in one listbox and time in other listbox and provide the support for selecting the date from one listbox and time from another listbox.

I hope you understand.

Do you have any suggestions let me know.

Thanks.

Re: Distinct data in listbox

Fri, 13 May 2011 14:11:17 GMT

Hi,

You can solve this problem in your script.

Tranform your field datetime in two fields one date and one time:

Test:

LOAD

vDateTime AS DateTime,

DATE(vDateTime, 'DD/MM/YYYY') AS Date,

SubField(vDateTime, ' ', 1) AS Time,

INLINE[

vDateTime

01/01/2011 10:10:00

01/01/2011 10:10:35

01/01/2011 11:00:00 ];

This way you have a date and time separate how we said.

Re: Distinct data in listbox

Mon, 27 Jun 2011 14:55:17 GMT

Hi Eduardo,

Can i Update the "DatesField" with old and new dates. Here the "DatesField" is getting only from 01-06-2011 10:59:59 to 16-06-2011 10:09:59 but not adding the before three dates i.e; from 29-05-2011 10:59:59 to 31-05-2011 12:59:59

Pls check the below code: Whether it is correct or wrong.

Sample:

LOAD * Inline

[DatesField

29-05-2011 10:59:59

30-05-2011 11:59:59

31-05-2011 12:59:59];

Concatenate

LOAD

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 ];

Regards

Ravi

Distinct data in listbox

Mon, 27 Jun 2011 15:20:08 GMT

Sorry, i don't understand very well.

When use the sintaxe CONCATENATE all tables need to have equals fields

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];

Concatenate

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 ];

Distinct data in listbox

Tue, 28 Jun 2011 08:17:24 GMT

When use the sintaxe CONCATENATE all tables need to have equals fields

I don't think so...

If the tables have equals fields, then there is an implicit CONCATENATE.

But the usage of "CONCATENATE" forces the tables to be concatened, even if they don't have the same fields. The resulting table will contains all the fields of the 2 source tables.

The lines where there is no value for a field will have a "NULL" value.

With the following tables, if you use a "concatenate" between "Table A" and "Table B", you will have the following resulting table :

In the case of Ravi, I agree with you that the "CONCATENATE" is not the right syntax to use. He wants to add fields on existing lines, not on new lines.

A "LEFT JOIN" would be more appropriate...

Distinct data in listbox

Tue, 28 Jun 2011 12:30:38 GMT

Sorry for my suggest

I said thinking in the case of ravi, i don't understand very well what he want.

You are correct about the CONCATENATE sintaxe,

When don't have equals fields the null values are inserted, so how i said

In this case i think that what ravi want are to add new lines in the table.

regards