Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Grabbing the latest value from a field

Hi,

I'm having a problem with grabbing the latest value from Field 2 & Field 3 WHERE Field 1 is DISTINCT. I'm trying to do a count on the total number of active users and for this to be accurate I'll need to get all the distinct users through the unique identifier and get their latest status where it is equal to active.

This is an example of the table I have:

Example of Current Table

UniqueIdentifierSegmentStatus
8181828 16:00:23Work PermitActive
8181828 16:00:23Work PermitInactive
9959599 06:56:23PassportSuspended
9959599 06:56:23LocalActive

Count of Active Users : 2. (Inaccurate)

Expected Result

UniqueIndentifierSegmentStatus
8181828 16:00:23Work PermitInactive
9959599 06:56:23LocalActive

Count of Active Users : 1. (Accurate)

Thanks in Advance,

Wilson.

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Grabbing the latest value from a field

Hi,

Try like this

[tmpData]:

LOAD *,

RowNo() AS RowNo

INLINE [

UniqueIdentifier, Segment, Status

9959599 06:56:23, Passport, Suspended

9959599 06:56:23, Local, Active

8181828 16:00:23, Work Permit, Active

8181828 16:00:23, Work Permit, Inactive

];

LEFT JOIN (tmpData)

LOAD

UniqueIdentifier,

Max(RowNo) AS RowNo,

1 AS IsLastRecord

Resident tmpData

GROUP BY UniqueIdentifier;

Now in chart use UniqueIdentifier and Segment as Dimensions

ExpressionSmiley Surprisednly({$<IsLastRecord={1}>}Status)

Regards,

Jagan.

7 Replies
sergey_maka8
Valued Contributor

Re: Grabbing the latest value from a field

Hi,

I did it using load script and set analysis

PFA an example

MVP
MVP

Re: Grabbing the latest value from a field

Hi,

Try like this

[tmpData]:

LOAD *,

RowNo() AS RowNo

INLINE [

UniqueIdentifier, Segment, Status

9959599 06:56:23, Passport, Suspended

9959599 06:56:23, Local, Active

8181828 16:00:23, Work Permit, Active

8181828 16:00:23, Work Permit, Inactive

];

LEFT JOIN (tmpData)

LOAD

UniqueIdentifier,

Max(RowNo) AS RowNo,

1 AS IsLastRecord

Resident tmpData

GROUP BY UniqueIdentifier;

Now in chart use UniqueIdentifier and Segment as Dimensions

ExpressionSmiley Surprisednly({$<IsLastRecord={1}>}Status)

Regards,

Jagan.

Not applicable

Re: Grabbing the latest value from a field

Hi, Sergey

Thanks for the help. Can you kindly break down the script and briefly explain it? Sorry, I'm a beginner in QlikView. How can I do a count on the number of active users then?

MVP
MVP

Re: Grabbing the latest value from a field

Hi,

Use the script in my previous post and for Counting the active users use this expression

Count({$<IsLastRecord={1}, Status={'Active'}>}Status)

or

Sum({$<IsLastRecord={1}, Status={'Active'}>}IsLastRecord)


Regards,

Jagan.



sergey_maka8
Valued Contributor

Re: Re: Grabbing the latest value from a field

You can count active users in two way

Use expressions

Sum({$<ShowLastRecord={1},Status={"Active"}>}ShowLastRecord)

or

Count({$<ShowLastRecord={1},Status={"Active"}>}UniqueIdentifier)

As FOR Script explanations:

I added RowNo to get a sequence of load

[tmpData]:

LOAD *, RowNo() AS RowNo INLINE [

UniqueIdentifier,    Segment,    Status

9959599 06:56:23,    Passport,    Suspended

9959599 06:56:23,    Local,    Active

8181828 16:00:23,    Work Permit,    Active

8181828 16:00:23,    Work Permit,    Inactive

];

Then I created additional table with only last RowNo for every Unique identifier. An I used Mapping load and then Apply created mapping table to avoid joins.

GrpData:

Mapping LOAD

LastRecord AS RowNo,

1 AS ShowLastRecord;

LOAD

UniqueIdentifier,

Max(RowNo) AS LastRecord

Resident tmpData

GROUP BY UniqueIdentifier;

Here I just add additional field ShowLastRecord which by default=0, but for the last record=1.

Data:

LOAD

*,

ApplyMap('GrpData',RowNo,0) AS ShowLastRecord

Resident tmpData;

DROP TABLE tmpData;

PFA an example

Not applicable

Re: Grabbing the latest value from a field

Hi, Sergey

Thanks alot for your help and explaination. It helps.

anbu1984
Honored Contributor III

Re: Grabbing the latest value from a field

[tmpData]:

LOAD *,

RowNo() AS RowNo

INLINE [

UniqueIdentifier, Segment, Status

9959599 06:56:23, Passport, Suspended

9959599 06:56:23, Local, Active

8181828 16:00:23, Work Permit, Active

8181828 16:00:23, Work Permit, Inactive

];

Final:

Load UniqueIdentifier, FirstSortedValue(Segment,-RowNo), FirstSortedValue(Status,-RowNo) Resident tmpData

Group by UniqueIdentifier;

Drop table tmpData;

Community Browser