Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Peony
Contributor

if condition in pivot table expression

Hi there!

I've got misunderstanding with qlik's expressions in pivot table.

Have a table with 3 fields: Date (contains date, no null values) , Category (contains names of differen data category, no null values for category 'Sales'), Digit (contains digits and some null value).

Whan I create pivot table, set as dimention Date and than Category. And the expression is: if (IsNull(Digit),'No data', Digit). But as the result, I have short table with only 'No data' values and withour other "not null values".

What am I doing wrong in this case?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: if condition in pivot table expression

Probably because you have more than one value of Digit in those cells. [Digit], as a naked field*, will return Null if there is more than one possible value in the pivot table cell. So that expression will not work quite that way. You may need to add Digit as a dimension as well.

* A naked field is a field reference not enclosed in an aggregation function (Sum(), Avg(), Min() etc). IsNull is not an aggregation function, so Digit is a naked field. See here for more Use Aggregation Functions!

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
MVP
MVP

Re: if condition in pivot table expression

Probably because you have more than one value of Digit in those cells. [Digit], as a naked field*, will return Null if there is more than one possible value in the pivot table cell. So that expression will not work quite that way. You may need to add Digit as a dimension as well.

* A naked field is a field reference not enclosed in an aggregation function (Sum(), Avg(), Min() etc). IsNull is not an aggregation function, so Digit is a naked field. See here for more Use Aggregation Functions!

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted
arvio124
New Contributor

Re: if condition in pivot table expression

Hi,

i think you have more than on result per line. it should work with:

      if(isnull(sum(Digit)),'No Data',sum(Digit))

Peony
Contributor

Re: if condition in pivot table expression

Yes! There is the mistake. You right! Thank you much!

Peony
Contributor

Re: if condition in pivot table expression

Hi. It works! Thank you for your idea.