topic FirstSortedValue Count in QlikView

FirstSortedValue Count

Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlikview Experts,

I am new to this domain and require some help.

I have a transactional table with a duplicate unique identifiers.

I would like to aggregate this table based on the last status the unique identifier was in.

Transactional Data
CommunicationId	Process Status LogId	Status Desc
1234	1	Status A
1234	2	Status B
1234	3	Status C
1234	4	Status D
1234	5	Status E	Lastest for that ID
5678	1	Status A
5678	2	Status B
5678	3	Status C
5678	4	Status D	Lastest for that ID
9123	1	Status A
9123	2	Status B
9123	3	Status C
9123	4	Status D	Lastest for that ID
4567	1	Status A
4567	2	Status B
4567	3	Status C
4567	4	Status D
4567	5	Status E	Lastest for that ID

Required Output
Status Desc	Count
Status E	2
Status D	2

Thanks in advance,

Yuvir

Re: FirstSortedValue Count

neelamsaroha157 — Mon, 19 Sep 2016 17:28:20 GMT

Check this out

Re: FirstSortedValue Count

alexdataiq — Mon, 19 Sep 2016 17:37:56 GMT

I'm sure there must be a simpler way, but I gave it a try:

[Transactional Data]:
LOAD * INLINE [
    CommunicationId, Process Status LogId, Status Desc
    1234, 1, Status A
    1234, 2, Status B
    1234, 3, Status C
    1234, 4, Status D
    1234, 5, Status E, 
    5678, 1, Status A
    5678, 2, Status B
    5678, 3, Status C
    5678, 4, Status D, 
    9123, 1, Status A
    9123, 2, Status B
    9123, 3, Status C
    9123, 4, Status D, 
    4567, 1, Status A
    4567, 2, Status B
    4567, 3, Status C
    4567, 4, Status D
    4567, 5, Status E, 
];


NoConcatenate
Output:
LOAD
  [Status Desc],
  Count([Status Desc]) as Count
Group by [Status Desc];
LOAD
  CommunicationId,
  LastValue([Process Status LogId]) as [Process Status LogId],
  LastValue([Status Desc]) as [Status Desc]
Resident [Transactional Data]
Group by CommunicationId;

DROP Tables [Transactional Data];

Re: FirstSortedValue Count

Anonymous — Mon, 19 Sep 2016 21:07:12 GMT

Temp_Table:

LOAD * INLINE [

CommunicationId, Process Status LogId, Status Desc

1234, 1, Status A

1234, 2, Status B

1234, 3, Status C

1234, 4, Status D

1234, 5, Status E,

5678, 1, Status A

5678, 2, Status B

5678, 3, Status C

5678, 4, Status D,

9123, 1, Status A

9123, 2, Status B

9123, 3, Status C

9123, 4, Status D,

4567, 1, Status A

4567, 2, Status B

4567, 3, Status C

4567, 4, Status D

4567, 5, Status E

];

Noconcatenate

Table:

Load max([Process Status LogId]) as [Process Status LogId],

CommunicationId

Resident Temp_Table group by CommunicationId;

Left join (Table)

Load * Resident Temp_Table;

Drop Table Temp_Table;

Re: FirstSortedValue Count

sunny_talwar — Tue, 20 Sep 2016 01:20:01 GMT

May be like this in a straight table

Dimension

=Aggr(FirstSortedValue([Status Desc], -[Process Status LogId]), CommunicationId)

Expression

Count(DISTINCT CommunicationId)

Re: FirstSortedValue Count

Tue, 20 Sep 2016 06:08:59 GMT

Hi Sunny,

Thank you for your assistance, i seem to be getting the correct figure, but only when i select the relevant status:

The incorrect figures:

The correct figure when i filter on the description:

Any ideas? i appreciate the help.

Thanks,

Yuvir

Re: FirstSortedValue Count

sunny_talwar — Tue, 20 Sep 2016 17:58:32 GMT

Would you be able to share your application to dig deeper to understand the reason for this?

Preparing examples for Upload - Reduction and Data Scrambling

Re: FirstSortedValue Count

Wed, 21 Sep 2016 05:55:26 GMT

Hi Sunny,

As i created a new app to upload for you to have a look at, I noticed that the values were now correct.

So i created a new sheet in my current app with the exact same calculated dimension and expression and it is now returning the correct result. There were absolutely no filter criteria selected in the original sheet but weirdly it did not seem to work.

Thank you so much for your assistance, i really appreciate it.

Yuvir