Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
New Contributor

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

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!

1 Solution

Accepted Solutions
Highlighted
Honored Contributor III

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

Use as expression in a st table and hide it

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

View solution in original post

7 Replies
Highlighted

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

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

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

Highlighted
Honored Contributor III

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

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

;

Highlighted

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

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor

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

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!

Highlighted
New Contributor

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

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!

Highlighted
Honored Contributor III

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

Use as expression in a st table and hide it

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

View solution in original post

Highlighted
Esteemed Contributor III

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

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