topic Re: firstsorted value in if isnull function in QlikView

firstsorted value in if isnull function

anisha_nan — Sat, 16 Nov 2024 03:51:35 GMT

Hi,

In the straight table i need to display project name,date,id and Purchase rate

1. I have requirement to get latest Purchase Rate on the basis of date for the particular Project.

2. If the Purchase Rate is Null for the specific line item then automatically it should pick the latest Purchase rate on the basis of date wherever the Rate is available .

i have try with above expression :

FirstSortedValue(DISTINCT Project Name,-Aggr(avg([Purchase Rate]),[Project Name])).

Can please correct me on the expression for the above requirement.

Regards

Re: firstsorted value in if isnull function

asinha1991 — Mon, 15 Apr 2019 11:00:50 GMT

if I understand your Q correctly try this under isnull

check for syntax

FirstSortedValue(Purchase Rate,-Aggr(max(if (not isnull(Purchase Rate),Date)),[Project Name])).

Re: firstsorted value in if isnull function

anisha_nan — Mon, 15 Apr 2019 11:12:07 GMT

Hi ,

Sorry..Expression is not working in straight table.

Regards

Re: firstsorted value in if isnull function

tresB — Mon, 15 Apr 2019 11:12:55 GMT

To ignore the null values, you could use set analysis, like:

FirstSortedValue(DISTINCT {<[Purchase Rate]={'*'}>} Project Name,
-Aggr(avg([Purchase Rate]),[Project Name]))

Re: firstsorted value in if isnull function

sunny_talwar — Mon, 15 Apr 2019 11:39:03 GMT

@anisha_nan wrote:
FirstSortedValue(DISTINCT Project Name,-Aggr(avg([Purchase Rate]),[Project Name])).

Are you looking to display Purchase Rate or Project Name? The above expression will display the project name based on the max Avg Purchase rate among different Project Names. Your description of the problem deviates from your expression. Would you be able to share a sample and provide the expected output from the sample data shared?

Re: firstsorted value in if isnull function

anisha_nan — Mon, 15 Apr 2019 13:29:34 GMT

Hi,
Sample data send needs the Output in straight table.
Please find the attached file.
Do we need to achieve the above requirement at the script level or directly we can add a measure in the straight table in order to achieve Purchase rate.

Regards

Re: firstsorted value in if isnull function

sunny_talwar — Mon, 15 Apr 2019 19:41:19 GMT

I am not sure if what is provided is the expected output or the sample data.... if it's either of them, then where is the other one 🙂

Re: firstsorted value in if isnull function

anisha_nan — Tue, 16 Apr 2019 05:25:22 GMT

Hi Sunny,

Its sample data and the and in the straight table i am using the same dimension and also mention what needs to consider for Purchase rate calculation.

Regards

Re: firstsorted value in if isnull function

sunny_talwar — Tue, 16 Apr 2019 11:02:35 GMT

But what are the exact values you are hoping to get? I am not able to translate your statement to numbers 🙂

Re: firstsorted value in if isnull function

asinha1991 — Tue, 16 Apr 2019 20:58:43 GMT

this works for me(in expression) in straight table provided

a. There is just one entry for latest date for a given project

b. Latest date doesnt have null rate

FirstSortedValue(Rate,-Aggr(max(Date),[Project Name]))

the data set you gave is confusing , it has multiple entries for same date...as per what i understand there will be just one entry for latest date

Re: firstsorted value in if isnull function

anisha_nan — Wed, 17 Apr 2019 09:23:11 GMT

Hi Sunny,

Please find the attached Data Set in which i have mentioned what needs to come in the Purchase rate .

I hope its clear now: smileyhappy:

Regards

Re: firstsorted value in if isnull function

sunny_talwar — Wed, 17 Apr 2019 11:27:47 GMT

It makes sense most of the time, expect one thing... why do we have Supplier A, Invoice 1001, Pricing category Price1 repeat 2 times for each set of Project Name

How do I differentiate between these two rows in the each block... why don't we have this as the solution

600 because the combination for Supplier A, 1001, Price1 has 600 as it's most recent value.

Re: firstsorted value in if isnull function

anisha_nan — Wed, 17 Apr 2019 16:29:56 GMT

Hi Sunny,

I Forgot to mentioned that we have two pricing category and for this Specific Requirement to get Purchase Rate we need to check only for Price 1 with all combination.

Regards

Re: firstsorted value in if isnull function

sunny_talwar — Wed, 17 Apr 2019 16:35:09 GMT

Price 1 is what I am looking at 🙂

Re: firstsorted value in if isnull function

anisha_nan — Thu, 18 Apr 2019 14:52:53 GMT

Hi Sunny,

Please find the attached data set and the actual requirement is also mention in it.

Regards

Re: firstsorted value in if isnull function

sunny_talwar — Thu, 18 Apr 2019 15:10:57 GMT

Checking

Re: firstsorted value in if isnull function

sunny_talwar — Thu, 18 Apr 2019 17:04:47 GMT

Is this your data?

Project Name	Date	Supplier	Invoice No	Pricing category 	Rate
abc	1/2/2018	Supplier A	1001	Purachse Price	100
abc	1/2/2018	Supplier B	1001	Purachse Price	200
abc	1/2/2018	Supplier A	1001	Selling Price	150
abc	1/2/2018	Supplier A	1001	Selling Price	300
xyz	1/3/2019	Supplier A	1001	Purachse Price	
xyz	1/3/2019	Supplier B	1001	Purachse Price	
xyz	1/3/2019	Supplier A	1001	Selling Price	400
xyz	1/3/2019	Supplier A	1001	Selling Price	
def	1/5/2018	Supplier A	1001	Purcahse Price	
def	1/5/2018	Supplier B	1001	Purcahse Price	500
def	1/5/2018	Supplier A	1001	Selling Price	600
def	1/5/2018	Supplier A	1001	Selling Price	700

or is this your data

Project Name	Date	Supplier	Invoice No	Pricing category 	Rate
abc	1/2/2018	Supplier A	1001	Purachse Price	100
abc	1/2/2018	Supplier B	1001	Purachse Price	200
abc	1/2/2018	Supplier A	1001	Selling Price	150
abc	1/2/2018	Supplier B	1001	Selling Price	300
xyz	1/3/2019	Supplier A	1001	Purachse Price	
xyz	1/3/2019	Supplier B	1001	Purachse Price	
xyz	1/3/2019	Supplier A	1001	Selling Price	400
xyz	1/3/2019	Supplier B	1001	Selling Price	
def	1/5/2018	Supplier A	1001	Purcahse Price	
def	1/5/2018	Supplier B	1001	Purcahse Price	500
def	1/5/2018	Supplier A	1001	Selling Price	600
def	1/5/2018	Supplier B	1001	Selling Price	700

Re: firstsorted value in if isnull function

anisha_nan — Fri, 19 Apr 2019 05:59:09 GMT

Hi,

Please ignore all the data set which was send earlier and refer only the data set which was send yesterday.

Regards