Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
dmanbbg24
Contributor
Contributor

Create table for all values matching only 1 comparison field value

I've got an expression problem I'm looking for some help with.

My data looks like:

field_counter|week_num
z |4
x |4
y |3
x |3
y |2
x |2
y |1
x |1


New week data is continually added. I want to create a table filtered for those fields appearing in the +latest week only+, i.e. newly used fields. In the above case that would be 'z'.

I can get a count using:

Count({<field_counter = e({<week_num -= {'$(=MaxString(week_num))'}>} field_counter)>} DISTINCT field_counter)

And create a table filter using (where the latest week is '4'):

=aggr(if(match(week_num, '1', '2', '3')=0, field_counter),field_counter)

But I need to replace the hardcoded list of prior weeks with, essentially, 'all weeks that are not MaxString(week_num)'. Tried 'not match' and 'only' with no luck.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

How about this

=Aggr(Only({<field_counter = e({<week_num -= {'$(=MaxString(week_num))'}>} field_counter)>} field_counter), field_counter)

View solution in original post

4 Replies
sunny_talwar

Can you explain the highlighted part of the expression

Count(<field_counter = e({<week_num -= {'$(=MaxString(week_num))' <GO>>} field_counter)>} DISTINCT field_counter)

Is this expression even working for you?

dmanbbg24
Contributor
Contributor
Author

sorry, fixed that now
sunny_talwar

How about this

=Aggr(Only({<field_counter = e({<week_num -= {'$(=MaxString(week_num))'}>} field_counter)>} field_counter), field_counter)
dmanbbg24
Contributor
Contributor
Author

great, thanks, that produces the same output