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

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

Anonymous — Fri, 11 May 2018 13:47:04 GMT

Hi guys, quick question. I would like to have an expression that would only show rows where Value and/or Status values are blank.

'Blank':

Load* Inline [

DateBlank| 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:

DateBlank 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!

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

Miguel_Angel_Baeyens — Fri, 11 May 2018 13:50:04 GMT

Add a condition at the end, before the ";"

WHERE Len(Trim(Value)) = 0 OR Len(Trim(Status)) = 0

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

sasiparupudi1 — Fri, 11 May 2018 13:57:47 GMT

'Blank':

Load* Inline [

DateBlank| 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 '|')

Where (Len(trim(Value))=0 or Len(trim(Status))=0 )

;

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

Anil_Babu_Samineni — Fri, 11 May 2018 13:59:21 GMT

May be this?

Sum({<Status = {"=Len(Status)=0"}>} Value)

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

Anonymous — Fri, 11 May 2018 13:59:52 GMT

Hi Miguel,

Thanks for your prompt reply. I was wondering how I could implement this on the front-end, as expression for an object and not in the script.

Thanks!

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

Anonymous — Fri, 11 May 2018 14:00:15 GMT

Hi Sasidhar,

Thanks for your prompt reply. I was wondering how I could implement this on the front-end, as expression for an object and not in the script.

Thanks!

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

sasiparupudi1 — Fri, 11 May 2018 14:04:10 GMT

Use as expression in a st table and hide it

(Len(trim(Value))=0 or Len(trim(Status))=0 )

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

vishsaggi — Fri, 11 May 2018 14:18:31 GMT

IS this your real requirement in your actual data? Just wondering why you want to exclude values when you have values in both? May be you can pull all the data and use flag to show only what you want like below:

With reference to Miguels expr:

1. To exclude values use below:

'Blank':

LOAD *

WHERE BlankFlg = 1;

Load *, IF(Len(Trim(Value))= 0 OR Len(Trim(Status)) = 0, 1, 0) AS BlankFlg Inline [

DateBlank| 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 '|');

OR Try like below where you get everything and yo ucan use BlankFlg to show or hide data.

'Blank':

Load *, IF(Len(Trim(Value))= 0 OR Len(Trim(Status)) = 0, 1, 0) AS BlankFlg Inline [

DateBlank| 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 '|');