Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
kolakiran
New Contributor II

How to get distinct values for the fields

Hi Experts,

How to get distinct values for the fields,  i have filed called PROGRESS

Progress -->Completed, In progress, Late, I have Student  field

    1. If a student is enrolled into two programs:  one is completed and other in progress : count towards IN-PROGRESS only
    2. If a student is enrolled into two programs:  one is completed and other in late : count towards LATE only
    3. If a student is enrolled into two programs:  one is late and other in progress : count towards IN-PROGRESS only

How can i achive this?

Name     Progress   Program

Kiran       Complete  1

Kiran        In Progress 2

Vijay        Complate  1

Anil          Inprogress  1

Anill         Late       2

kiran should count only for in progress

anil should count for late

vijay should count in Complete

1 Solution

Accepted Solutions

Re: How to get distinct values for the fields

May be this

=Sum(Aggr(If(FirstSortedValue(TOTAL <Name> Progress, Number) = Progress, 1, 0), Name, Progress))

Where Number is created in the script like this

Table:

LOAD *,

Dual(Progress, Match(Progress, 'In Progress', 'Late', 'Complete')) as Number;

LOAD * INLINE [

    Name, Progress, Program

    Kiran, Complete, 1

    Kiran, In Progress, 2

    Vijay, Complate, 1

    Anil, In Progress, 1

    Anil, Late, 2

];

11 Replies

Re: How to get distinct values for the fields

anil should count in in-progress, right?

Capture.PNG

kolakiran
New Contributor II

Re: How to get distinct values for the fields

yes In progress Only

Re: How to get distinct values for the fields

May be this

=Sum(Aggr(If(FirstSortedValue(TOTAL <Name> Progress, Number) = Progress, 1, 0), Name, Progress))

Where Number is created in the script like this

Table:

LOAD *,

Dual(Progress, Match(Progress, 'In Progress', 'Late', 'Complete')) as Number;

LOAD * INLINE [

    Name, Progress, Program

    Kiran, Complete, 1

    Kiran, In Progress, 2

    Vijay, Complate, 1

    Anil, In Progress, 1

    Anil, Late, 2

];

kolakiran
New Contributor II

Re: How to get distinct values for the fields

It didnt worked  , I am looking total count of In Progress , Total Count of Complete, Total Count Late seperatly

Re: How to get distinct values for the fields

Separately? What do you mean? You don't want this

Capture.PNG

kolakiran
New Contributor II

Re: How to get distinct values for the fields

Hi Sunny,

Actually if kiran is Complete  and also In Progress  we have to count kiran in In progress only not Complete

in this case output is  

Kiran  Inprogress 

Anil    Late

Vijay  Complete

Count of In progress towards student is   Complete 1, In progress 1, Late 1

Re: How to get distinct values for the fields

But you didn't you just agree that Anil should be in In Progress bucket also?

Kiran  Inprogress

Anil    Inprogress

Vijay  Complete

That is why Inprogress = 2 and Complete = 1... what is the issue my friend?

kolakiran
New Contributor II

Re: How to get distinct values for the fields

Anil Should be in Late Bro..  because  Anil is in In Progress and late so he should be in late

The total counts will be like this

Complete =1

In Progress = 1

Late =1

and If the person is in Complete and Late then  it should count only Late

Re: How to get distinct values for the fields

Look at the requirement again

Capture.PNG

According to c., because Anil is late and in progress, you want it to be counted towards IN-PROGRESS....

And then later you confirmed this also

Capture.PNG

Look above.... now are you fooling around with me or you don't know what you want?

Community Browser