Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashishpalkar
Creator III
Creator III

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
sunny_talwar

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)

View solution in original post

5 Replies
sunny_talwar

May be this

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

Group By F1;

ashishpalkar
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

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)