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

[Anonymous]

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!

[Miguel_Angel_Baeyens]

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

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

[sasiparupudi1]

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

;

[Anil_Babu_Samineni]

May be this?

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

[Anonymous]

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!

[Anonymous]

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!

[sasiparupudi1 · Accepted solution]

Use as expression in a st table and hide it

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

[vishsaggi]

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