Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
LiorDeshe
Contributor II
Contributor II

Ignore Null values in firstsortedvalue

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 ?

 

 

Labels (3)
1 Solution

Accepted Solutions
LiorDeshe
Contributor II
Contributor II
Author

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 ?

 

View solution in original post

5 Replies
Or
MVP
MVP

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
Contributor II
Contributor II
Author

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

Unexpected token: '{'

Or
MVP
MVP

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
Contributor II
Contributor II
Author

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_
Specialist
Specialist

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