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: 
nishanthi_8
Creator
Creator

Load the latest record using resident load

I have a table where i used to concatenate the excel sheets based on monthly basis and i have City and MonthYear column which is of MMM-YY data type. Now in resident load i should be able to get the latest city where he / she is in.. And I tried the following :

Convert_Date:

Load

  [User ID],

  City ,

  date(MonthYear,'MMYY') AS MY

Resident [Emp];

Max_Date:

load

   

   max(DATE(MY,'MMYY')) AS M

 

resident Convert_Date Group by [User ID]  ;

But I am not able to get the latest city.

8 Replies
tresesco
MVP
MVP

Try like:

Convert_Date:

Load

  [User ID],

  City ,

  date#(MonthYear,'MMYY') AS MY

Resident [Emp];

Max_Date:

load

  [User ID],

   Date(max(MY)) AS M,

   firstSortedValue(City, -MY) as LatestCity

resident Convert_Date Group by [User ID]  ;

nishanthi_8
Creator
Creator
Author

in latest city I couldn't find any data

tresesco
MVP
MVP

Try like:

firstSortedValue(Distinct City, -MY) as LatestCity


Note: You might see a warning for distinct, just ignore that.

nishanthi_8
Creator
Creator
Author

no this is not helping me out

tresesco
MVP
MVP

Could you post a qvw with sample data?

nishanthi_8
Creator
Creator
Author

no thats some what confidential data. is there any other alternative way to find out the latest city?

nishanthi_8
Creator
Creator
Author

thanks i got it

Chanty4u
MVP
MVP

try using this

FirstSortedValue - script function ‒ QlikView

FirstSortedValue - script function

FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument, taking into account rank, if specified. If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL.

The sorted values are iterated over a number of records, as defined by a group by clause, or aggregated across the full data set if no group by clause is defined.

Syntax:

FirstSortedValue ([ distinct ] value, sort-weight [, rank ])

Return data type: dual

Arguments:

ArgumentDescription
value ExpressionThe function finds the value of the expression value that corresponds to the result of sorting sort_weight.
sort-weight ExpressionThe expression containing the data to be sorted. The first (lowest) value of sort_weight is found, from which the corresponding value of the value expression is determined. If you place a minus sign in front of sort_weight, the function returns the last (highest) sorted value instead.
rank Expression

By stating a rank "n" larger than 1, you get the nth sorted value.

distinct

If the word DISTINCT occurs before the function arguments, duplicates resulting from the evaluation of the function arguments are disregarded.

Examples and results:

Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in our document to see the result.

ExampleResult
Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue:

LOAD Customer,FirstSortedValue(Product, UnitSales) as MyProductWithSmallestOrderByCustomer Resident Temp Group By Customer;

Customer MyProductWithSmallestOrderByCustomer

Astrida CC

Betacab AA

Canutility AA

Divadip DD

The function sorts UnitSales from smallest to largest, looging for the value of Customer with the smallest value of UnitSales, the smallest order.

Because CC corresponds to the smallest order (value of UnitSales=2) for customer Astrida. AA corresponds to the smallest order (4) for customerBetacab, CC corresponds to the smallest order (8) for customer Canutility, and DD corresponds to the smallest order (10) for customer Divadip..

Given that the Temp table is loaded as in the previous example:

LOAD Customer,FirstSortedValue(Product, -UnitSales) as MyProductWithLargestOrderByCustomer Resident Temp Group By Customer;

Customer MyProductWithLargestOrderByCustomer

Astrida AA

Betacab DD

Canutility CC

Divadip -

A minus sign precedes the sort_weight argument, so the function sorts the largest first.

Because AA corresponds to the largest order (value of UnitSales:18) for customer Astrida, DD corresponds to the largest order (12) for customerBetacab, and CC corresponds to the largest order (13) for customerCanutility. There are two identical values for the largest order (16) for customer Divadip, therefore this produces a null result.

Given that the Temp table is loaded as in the previous example:

LOAD Customer,FirstSortedValue(distinct Product, UnitSales) as MyProductWithSmallestOrderByCustomer Resident Temp Group By Customer;

Customer MyProductWithLargestOrderByCustomer

Astrida AA

Betacab DD

Canutility CC

Divadip AA

This is the same as the previous example, except the distinct qualifier is used. This causes the duplicate result for Divadip to be disregarded, allowing a non-null value to be returned.