FirstSortedValue Count

[#1]

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

[neelamsaroha157]

Check this out

[alexdataiq]

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];

[Anonymous]

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;

[sunny_talwar · Accepted solution]

May be like this in a straight table

Dimension

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

Expression

Count(DISTINCT CommunicationId)

[#6]

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

[sunny_talwar]

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

[#8]

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