topic Re: Show single value if repeated in listbox in QlikView

Show single value if repeated in listbox

qv_bazz_10 — Wed, 10 Feb 2016 17:29:00 GMT

Hi everyone,

i have a list box with repeated values (specifically dates in format "DD/MM/YYYY") and i would like to "aggregate" them and see only one record if the value is the same, is there any way to do it?

Here's what i have now:

Here's what i would like to see:

Giorno pubblicazione flusso

08/02/2016

07/02/2016

06/02/2016

...

Thank in advance,

Andrea

Re: Show single value if repeated in listbox

sunny_talwar — Wed, 10 Feb 2016 17:31:44 GMT

I think you need to fix this in the script. Seems like your date field is actually timefield with time component in it.

Try this:

Date(Floor(DateField)) as DateField

and then create a list box for DateField.

Re: Show single value if repeated in listbox

qv_bazz_10 — Thu, 11 Feb 2016 08:18:43 GMT

Hi Sunny T,

what do you mean with "script"? Sorry, i'm a beginner with QV...i checked the configuration of the number format in the table box, this is what i see:

It's the same configuration i have in the list box. May you please tell me where do i have to insert your code?

Thanks again,

Andrea

Re: Show single value if repeated in listbox

jyothish8807 — Thu, 11 Feb 2016 08:24:40 GMT

Hi Andrea,

There is definitely an issue with you date field format, other wise list box will show only one entry for each.

So as Sunny mentioned you can do that also, or try like this:

Load

date(date#(Giorno pubblicazione flusso,'DD/MM/YYYY'),'DD/MM/YYYY') as Giorno pubblicazione flusso

resident <>

Regards

Re: Show single value if repeated in listbox

qv_bazz_10 — Thu, 11 Feb 2016 08:32:52 GMT

Hi Jyothish,

sorry, i'd like to try your fix or the Sunny one but i'm a real newbie and i don't understand where do i have to insert the code...sorry again and thanks for your help.

Andrea

Re: Show single value if repeated in listbox

jagan — Thu, 11 Feb 2016 08:45:53 GMT

Hi,

Changing in script is the best option, open the script window and search for this date field and replace it with

LOAD

Date(Floor(DateField)) as DateField

Note: Replace DateField with your actual field name.

Regards,

jagan.

Re: Show single value if repeated in listbox

qv_bazz_10 — Thu, 11 Feb 2016 09:02:37 GMT

Sorry again...i'm afraid i need a step-by-step help...what do you mean with the script window? Do i have to locate it somewhere in the table box properties o anywhere else?

Thank you very much,

Andrea

Re: Show single value if repeated in listbox

jyothish8807 — Thu, 11 Feb 2016 09:15:51 GMT

Hi Andrea,

You need to make changes in the backed script. Press Ctrl+E to go to script editor.

Over there is in load statement you need to make changes:

Load

date(date#(Giorno pubblicazione flusso,'DD/MM/YYYY'),'DD/MM/YYYY') as Giorno pubblicazione flusso

from <>

Regards

Re: Show single value if repeated in listbox

sunny_talwar — Thu, 11 Feb 2016 09:31:14 GMT

Not that script. For going into the script you need to press Ctrl + E and then the 'Script Editor' will show up. Search for this -> Giorno pubblicazione flusso and whereever this is getting created, either change this or create a new field like this

LOAD [Giorno pubblicazione flusso],

Date(Floor([Giorno pubblicazione flusso])) as Date

YourOtherFieldsAsIs

FROM/Resident ...

Try the above and let us know if you still have trouble.

Best,

Sunny

Re: Show single value if repeated in listbox

qv_bazz_10 — Thu, 11 Feb 2016 09:32:06 GMT

Ok, i understood why i couldn't find the script editor: the function is disabled because i'm operating on a limited user version, i should have administrator's access to do it...do you think it's the only way to do it or maybe there's another one without having to edit the script?

Thanks again,

Andrea

Re: Show single value if repeated in listbox

jonathandienst — Thu, 11 Feb 2016 09:45:31 GMT

Its is important to use Sunny T's script to remove the time portion. The format you are using hides the time differences, so they appear the same in the listbox.

NOTE Date(.., 'dd/MM/yyyy') does NOT remove the time portion, it merely changes the display format. You must have a Floor() in the expression.

Re: Show single value if repeated in listbox

jyothish8807 — Thu, 11 Feb 2016 11:04:47 GMT

Hi Andrea,

Will it be possible to share the app ? so it will be easy for us to help.

Regards

Re: Show single value if repeated in listbox

sunny_talwar — Thu, 11 Feb 2016 11:35:37 GMT

Try this as your list box expression:

=Date(Floor([Giorno pubblicazione flusso]))

Re: Show single value if repeated in listbox

qv_bazz_10 — Thu, 11 Feb 2016 13:34:00 GMT

Hi Sunny,

did i insert the code in the right place?

With this configuration the risult is "-" in the listbox for every date but maybe i did something wrong.

@Jyothish Kc

I'm afraid the sharing function is disabled by the admin too because i'm working on custom sheet part of a non-editable common document...

Thank you all for help and patience too.

Andrea

Re: Show single value if repeated in listbox

sunny_talwar — Thu, 11 Feb 2016 16:20:05 GMT

Try doing it like this:

Re: Show single value if repeated in listbox

sunny_talwar — Thu, 11 Feb 2016 16:32:47 GMT

Also what is the name of the field? Doesn't seem to be -> Giorno pubblicazione flusso

You will need to put the field name that originally comes from the script.

Re: Show single value if repeated in listbox

qv_bazz_10 — Thu, 11 Feb 2016 16:58:10 GMT

Got it! Thank you very much Sunny! And thanks to all the other users for your advices too.

Andrea

Re: Show single value if repeated in listbox

sunny_talwar — Thu, 11 Feb 2016 18:40:52 GMT

You need to thank Rob

I just proposed minor alternations

Re: Show single value if repeated in listbox

sunny_talwar — Thu, 11 Feb 2016 19:37:01 GMT

Awesome

I am glad we all were able to help you in one way or the other.

Best,

Sunny