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: 
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.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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

Expression:Only({$<IsLastRecord={1}>}Status)

Regards,

Jagan.

View solution in original post

7 Replies
SergeyMak
Partner Ambassador
Partner Ambassador

Hi,

I did it using load script and set analysis

PFA an example

Regards,
Sergey
jagan
Luminary Alumni
Luminary Alumni

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

Expression:Only({$<IsLastRecord={1}>}Status)

Regards,

Jagan.

Not applicable
Author

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?

jagan
Luminary Alumni
Luminary Alumni

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.



SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
Not applicable
Author

Hi, Sergey

Thanks alot for your help and explaination. It helps.

anbu1984
Master III
Master III

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