topic Re: Firstsortedvalue returns Null in QlikView

Firstsortedvalue returns Null

Fri, 08 May 2015 23:02:50 GMT

Hi everybody, I have the following expression:

FirstSortedValue(DescriptionFixed,Aggr(sum(Amount-OriginalAmt), DescriptionFixed))

Since I have exactly the same amount for two different items the firstsortedvalue brings me NULL.

How can I get just one item instead of NULL? Either item will work.

Any help will be greatly appreciate it

Re: Firstsortedvalue returns Null

mrossoit — Fri, 08 May 2015 23:38:25 GMT

Hi,

I can't try it at the moment.

Try to add distinct keyword:

FirstSortedValue(DISTINCT DescriptionFixed,Aggr(sum(Amount-OriginalAmt), DescriptionFixed))

Regards

Re: Firstsortedvalue returns Null

Gysbert_Wassenaar — Sat, 09 May 2015 06:46:35 GMT

You're already showing the value you want to calculate in the dimension column. Why do you think you need that expression?

Re: Firstsortedvalue returns Null

Sun, 10 May 2015 10:01:42 GMT

Hi,

Please try:

=FirstSortedValue(country,-Aggr(sum(Amount-OriginalAmt)+rank(DescriptionFixed,DescriptionFixed)/10,DescriptionFixed))

This will do the second sorting by alphabetical order (DescriptionFixed field).

Hope this helps.

Kind regards,

Nuno

Re: Firstsortedvalue returns Null

Mon, 11 May 2015 18:57:23 GMT

Hi Nuno, thank you very much it ran smoothly, I really appreciate your help.

What is the /10 stand for?

Best,

Eric !

Re: Firstsortedvalue returns Null

Mon, 11 May 2015 18:59:31 GMT

I already did Massimiliano, but did not work. Thanks for your help though

Nuno's approach worked perfectly

Re: Firstsortedvalue returns Null

Tue, 12 May 2015 10:32:54 GMT

Hi Eric,

Thanks. I can't take all the credit for the formula, because it's adapted from cleveranjos‌.

The /10 is only to get it in a small factor that the second sort doesn't mess up the first.

Kind regards,

Nuno

Re: Firstsortedvalue returns Null

Thu, 14 May 2015 16:10:30 GMT

Hi Nuno. I have another question for you. I just created a backorder app:

Our ideal billing process is for every Cust PO one invoice is created, however that is not happening and my App is showing unreal BO.

Example:

CustPO: 0029075 is related with 2 invoices: SI015361 and SI015362. This causes that the report show unreal BO, for example the first LineNo_"200000" on the report has $1,706 on PO Amount, but 0 invoiced, meaning that the line is consider as a BO, however the second row same item and same LineNo_"200000" show $1,706 on PO Amount and $1,706 invoiced. Hence the first line it is not a BO.

I think that writing an IF statement on the script can be a good approach to not show duplicated keys, or even better firstsortedvalue, since I need to show the value that has Invoiced amount and prevent the unreal BO, but I do not know how to do it. Any thoughts?

KDup

0029075|200000

0029075|290000

Thank you very much for your help..

Best,

Eric

Re: Firstsortedvalue returns Null

Fri, 15 May 2015 10:37:39 GMT

Hi Eric,

Looking at you data I would just do something like:

Load *

where Invoiced (1st column) = 'Invoiced'

Load *

where Invoiced (11th column) <> 0

One of those should remove the duplications. depending on the rule you prefer.

Firstortedvalue or other more complicated solutions are only necessary if your request is more complex than I understood.

Kind regards,

Nuno

Re: Firstsortedvalue returns Null

Fri, 15 May 2015 15:46:21 GMT

Hi Nuno, thanks you very very much for the tip, however I still need to Show the Invoiced=(backorder) on the first approach and on the second there can be situations where there is 0 on the eleven column meaning that there was zero inventory hence a BO.

I was trying to use de Dup key that I created with WHERE but the result it is not correct, this is part of the script:

// Help to create a concatenate field from diferent tables and filter duplicated Backorders

BODdupTemp:

Load Distinct

Invoice As Invoice,

"#CustPO" As KCustPO

Resident SalesInvoice;

Join

Load Distinct

Invoice As Invoice,

"Line No_" As KLineNo

Resident SalesInvoiceline;

SalesInvoiceline:

Load Distinct

Invoice&'|'&KLineNo AS Key,

KCustPO&'|'&KLineNo As KDup

Resident BODdupTemp;

where KDup <> KDup;

Re: Firstsortedvalue returns Null

Fri, 15 May 2015 17:13:33 GMT

Sorry Eric. I don't know how to help in this case...but this is a community, there are always people who understand some issues better than others.

Re: Firstsortedvalue returns Null

Wed, 20 May 2015 15:05:52 GMT

Don't worry thank you very much.

Have a great day!

Re: Firstsortedvalue returns Null

IAMDV — Sun, 31 May 2015 09:42:54 GMT

Hi Eric,

Are you still looking for the solution? I realise that it's 2 weeks old thread.

If yes, so you don't mind getting either value when there's clash?

Cheers,

www.QlikShare.com

Re: Firstsortedvalue returns Null

Tue, 02 Jun 2015 18:35:07 GMT

Hi Deepak, thanks for the following up, the first question has been answered, but I am struggling on the second one, any advice?

I just created a backorder app:

Our ideal billing process is for every Cust PO one invoice is created, however that is not happening and my App is showing unreal BO.

Example:

Thanks for your help.

Re: Firstsortedvalue returns Null

Valentino_Rossi_46 — Mon, 20 May 2019 17:32:18 GMT

Hola Nuno me podrías ayudar

No quiero nulos ni vacíos

= FirstSortedValue(Cliente,Aggr(sum(if(Televisora='TVMAS' and Año='2019',Value))/SUM(IF(Año='2019',Value)),Cliente))

Saludos!!!