Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
UniqueIdentifier | Segment | Status |
---|---|---|
8181828 16:00:23 | Work Permit | Active |
8181828 16:00:23 | Work Permit | Inactive |
9959599 06:56:23 | Passport | Suspended |
9959599 06:56:23 | Local | Active |
Count of Active Users : 2. (Inaccurate)
Expected Result
UniqueIndentifier | Segment | Status |
---|---|---|
8181828 16:00:23 | Work Permit | Inactive |
9959599 06:56:23 | Local | Active |
Count of Active Users : 1. (Accurate)
Thanks in Advance,
Wilson.
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.
Hi,
I did it using load script and set analysis
PFA an example
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.
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?
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.
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
Hi, Sergey
Thanks alot for your help and explaination. It helps.
[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;