How to show only rows with 1 or 2 missing data in table?

[mrichman]

Hi Community, would be wonderful if you could help.

I would like to have an expression (Straight Table) where the table only show rows where Value and/or Status values are blank. (Not in the script)

'Data':

Load* Inline [

Date | Value | Status

21-04-2018 | 321 | 1

22-04-2018 | 421 | 2

23-04-2018 |  |

24-04-2018 | 123 | 1

25-04-2018 | 431 |

26-04-2018 | 212 |

27-04-2018 | | 2

28-04-2018 | 434 | 1

29-04-2018 | |

30-04-2018 | |1

] (delimiter is '|');

Ideal result in Qlik Sense Table:

Date              Value      Status

23-04-2018

25-04-2018    431

26-04-2018    212

27-04-2018                      2

29-04-2018

30-04-2018                      1

Thanks in advance!

[isingh30]

What exactly is your requirement? First line says you need blank values then your ideal result shows with & without values both. You want to show some specific values under Value & Status or not?

Thank you!

[maxgro]

maybe with a calculated dimension

=aggr(if(Sum(Value)=0 or sum(Status)=0, Date), Date)

and the expressions

sum(value) sum(status)

[mrichman]

Hi Ishtdeep,

Thanks for the prompt reply and question. The main goal is to have a table where only rows with blank 'Value' and/or 'Status' are shown. We want to know on which date we have a blank 'Value' and/or 'Status'. Would be amazing if you could help.

Thanks in advance!

[mrichman]

Hi Massimo,

Thank you for your prompt reply. For Qlikview the solution was:  =(Trim(len(Blank.Status)=0) or trim(Len(Blank.Value)=0)=-1)

The formula was added into the expression tab.

It basically just showed rows whereby Value and/or Status was missing and hid the rest. It was then not needed to filter the table anymore.

However, for Qlik Sense I'm not sure if there is any similar solution but would we great if you guys can recommend anything.

Thanks in advance

[isingh30 · Accepted solution]

Please check this -

Thank you !

[mrichman]

Yes, indeed. Something like this do you have the qvf file? Thanks in advance.

[isingh30]

Yeah. Please close the thread.

Thank you!

[mrichman]

‌thread closed, could you please send/publish the file?