Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ashishpalkar
Contributor II

Find distinct values from columns of loaded straight table.

Hello

Here are my scripts:

TestTemp:

LOAD * INLINE [
F1, F2, F3
1, a, 4234
2, aaaa, 2342
2, b, 24
3, c, 34
4, c, 64
5, deee, 5345
5, e, 6754
5, e, 545
]
;

Test:
load Distinct
F1,
F2
Resident TestTemp;

Drop Table TestTemp;

The result is:

F1F2
1a
2aaaa
2b
3c
4c
5deee
5e

What I want is distinct F1. Create a KPI and show Distinct values of columns F1.

So F1 value should be 1 2 3 4 5. Because F2 has two values for 2 and 5, 2 and 5 in F1 appeared twice.

I want to only take the first value  in F2 as F2, so that the final result is

F1F2
1a
2aaaa
3c
4c
5deee

How can I achieve that? Can anyone help please? Thanks!

1 Solution

Accepted Solutions

Re: Find distinct values from columns of loaded straight table.

How did we end up here? Lol.... anyways try this may be

Count(DISTINCT If(fabs(Sum(Quantity_PT)) - fabs(Sum(Quantity_AT)) <> 0, AccId))

or this

Count(DISTINCT {<AccId = {"=fabs(Sum(Quantity_PT)) - fabs(Sum(Quantity_AT)) <> 0"}>} AccId)

5 Replies

Re: Find distinct values from columns of loaded straight table.

May be this

Test:
LOAD F1,
    FirstValue(F2) as F2
Resident TestTemp

Group By F1;

ashishpalkar
Contributor II

Re: Find distinct values from columns of loaded straight table.

Thanks Sunny,

I am looking show these distinct values from column F1 in KPI too. how to achieve this?

Re: Find distinct values from columns of loaded straight table.

Forget about QlikView, how would you visualize this? A concatenated list of all F1s or 5 different KPIs for each of the F1s or what?

ashishpalkar
Contributor II

Re: Find distinct values from columns of loaded straight table.

in below example , i want to find out account ID with non null values.

if((FABS(sum(Quantity_PT))-FABS(SUM(Quantity_AT)))<>0,count(distinct AccId),null())

Re: Find distinct values from columns of loaded straight table.

How did we end up here? Lol.... anyways try this may be

Count(DISTINCT If(fabs(Sum(Quantity_PT)) - fabs(Sum(Quantity_AT)) <> 0, AccId))

or this

Count(DISTINCT {<AccId = {"=fabs(Sum(Quantity_PT)) - fabs(Sum(Quantity_AT)) <> 0"}>} AccId)

Community Browser