Ignore Null values in firstsortedvalue

[LiorDeshe]

Hi Everybody

I have the following table:

Case	Date	A	B	C
1	05.11.2023	null	null	null
1	06.11.2023	1	null	null
1	07.11.2023	2	5	null
1	08.11.2023	null	null	8
1	09.11.2023	null	null	null

I need to get the first Values if columns A,B and C which are not null, sorted by date.

Case	A	B	C
1	1	5	8

The solution in  Solved: How to I grab the first non-NULL value for each co... - Qlik Community - 1214727 , Does work and returns Nulls.

Any idea how to do it ?

[Or]

I believe you should be able to achieve this with a simple set eliminating the nulls, e.g.

FirstSortedValue({< A= {*}>}A,Date)

FirstSortedValue({< B= {*}>}B,Date)

FirstSortedValue({< C= {*}>}C,Date)

[LiorDeshe]

Thanks, I should have mentioned I tried it, but got the following error in the load:

Unexpected token: '{'

[Or]

Sounds like you're trying to apply it in script, whereas the set solution is intended for front-end expressions. Scriptside, you'd want something like:

Load Case, Date, FirstSortedValue(A, Date) as A

From SomeTable

Where not isnull(A);

Join

Load Case, Date, FirstSortedValue(B, Date)

From SomeTable

Where not isnull(B);

Join

Load Case, Date, FirstSortedValue(C, Date)

From SomeTable

Where not isnull(C);

[LiorDeshe · Accepted solution]

Thanks !

It's not quite working with the Date as A,B & C can show up at any date thus duplicating results per case. The following worked:

Case_Attributes:

LOAD Case,

FirstSortedValue(A, Date) as A

Resident Root

where not isnull(A)

Group By Case;

Join

LOAD Case,

FirstSortedValue(B, Date) as B

Resident Root

where not isnull(B)

Group By Case;

...

I have 34 attributes, is there a more efficient way, rather then 33 joins ?

[vincent_ardiet_]

You can do this in a loop using fieldname to generate the code dynamically.