topic Re: Pick one value among duplicates values in QlikView

Pick one value among duplicates values

hitha1512 — Mon, 24 Apr 2023 07:36:09 GMT

Hello Team,

I have output of data which looks like below. For one date I have 2 tickets giving me value 12 hours time for each, for same baseitem. In such cases where perday per baseitem if i have same values under Time, I should pick only one ticket or show only one value which is 12.

This needs to be done at backend as I have many scenarios like this.

Required output: Any one ticket

Any help would be appreciated.

Re: Pick one value among duplicates values

hic — Mon, 24 Apr 2023 07:56:03 GMT

If you don't use Number as dimension, you will get what you want:

Dimensions:
- Date
- BaseItem

Measures:
- If(Count(Distinct Number)=1,Only(Number),'Several') labelled 'Number'
- Sum(Time)/Count(Distinct Number) labelled 'Time'

Re: Pick one value among duplicates values

hitha1512 — Mon, 24 Apr 2023 10:20:22 GMT

@hic Thank you for your quick response 🙂 If i have to apply this at the backend code ? how would that work ?

Since I have multiple charts, I would like this to be done at the backend code level.

I need to pick the first or any row when one baseitem for a particular date has same time values for multiple numbers.

Re: Pick one value among duplicates values

BrunPierre — Mon, 24 Apr 2023 14:27:33 GMT

NoConcatenate
LOAD BaseItem,
DateTest1,
Time,
FirstValue(Number) as Number

Resident SourceTable;
Group By BaseItem,
DateTest1,
Time;
DROP Table SourceTable;

Re: Pick one value among duplicates values

hitha1512 — Thu, 04 May 2023 04:40:39 GMT

Thanks a lot. That worked.

Re: Pick one value among duplicates values

Prem0212 — Thu, 04 May 2023 07:58:52 GMT

you can use distinct function in the front end or u can use group by condition in the data load editor and load the script.

Please like accept the solution if it is resolved.