Find distinct values from columns of loaded straight table.

[ashishpalkar]

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:

F1	F2
1	a
2	aaaa
2	b
3	c
4	c
5	deee
5	e

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

F1	F2
1	a
2	aaaa
3	c
4	c
5	deee

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

[sunny_talwar]

May be this

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

Group By F1;

[ashishpalkar]

Thanks Sunny,

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

[sunny_talwar]

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]

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())

[sunny_talwar · Accepted solution]

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)