Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mrichman
New Contributor III

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

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!

1 Solution

Accepted Solutions
isingh30
Contributor III

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

Please check this -

Screen.PNG

Thank you !

8 Replies
isingh30
Contributor III

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

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!

MVP
MVP

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

maybe with a calculated dimension

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

and the expressions

sum(value) sum(status)

mrichman
New Contributor III

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

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
New Contributor III

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

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
Contributor III

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

Please check this -

Screen.PNG

Thank you !

mrichman
New Contributor III

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

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

isingh30
Contributor III

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

Yeah. Please close the thread.

Thank you!

mrichman
New Contributor III

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

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

Community Browser