topic Re: if condition in pivot table expression in QlikView

if condition in pivot table expression

Peony — Tue, 20 Mar 2018 07:58:55 GMT

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?

Re: if condition in pivot table expression

jonathandienst — Tue, 20 Mar 2018 08:12:32 GMT

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!

Re: if condition in pivot table expression

arvio124 — Tue, 20 Mar 2018 08:14:30 GMT

Hi,

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

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

Re: if condition in pivot table expression

Peony — Tue, 20 Mar 2018 08:24:10 GMT

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

Re: if condition in pivot table expression

Peony — Thu, 22 Mar 2018 18:52:40 GMT

Hi. It works! Thank you for your idea.