Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ELIMINATE NULL ROWS

Attached  please find the sample qvw, sample Excel .  What I am trying to accomplish is as per the snapshot policy number 356541 has multiple file numbers assigned to it and also there is a null value assigned to the same policy number.  In a case like this where there is even 1 File number exist for a policy number  I want to eliminate the rows for the same policy number with null values, but if Null Value is the only entry available for a policy then I want that Null value for example in this  case I would like to see the row for policy number 36547.

7 Replies
Anil_Babu_Samineni

I am sorry, If i misunderstand over here

Load * From <Data Source> Where File <> Peek('File', -1);

Or

Load * From <Data Source> Where Previous(File) <> File;

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)
Not applicable

So  in my sample policy number 356541 has File numbers and a null, in this case I want to eliminate the row with Null  file number

Anil_Babu_Samineni

Now your question makes confuse, Would you share expected result on wall

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

You mean to say, You want to exclude all File Null values

Load * From <Data Source> Where Len(File)>0;

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)
Not applicable

Below is the expected result

58484356541
58495356541
58848356541
59794356541
59938356541
60322356541
61191

356541

36547

                 
Anil_Babu_Samineni

I really don't why you need this. For your question answer may be this

Load * From <Data Source> Where If(Previous(POL_POLICYNUMBER) = POL_POLICYNUMBER,Len(File)>0, POL_POLICYNUMBER)

Does that answer your question?

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

Hi Rehan,

Data:

LOAD File,

     POL_POLICYNUMBER

FROM

[Sample (2).xls]

(biff, embedded labels, table is Sheet1$);

Left Join

Load POL_POLICYNUMBER,

    Count(File) as FileCount

Resident Data Group by POL_POLICYNUMBER;

NoConcatenate

Result:

Load *

Resident Data

Where (FileCount > 0 And Len(Trim(File))>0) Or FileCount =0;

DROP Field FileCount;

DROP Table Data;

Output:

Capture.PNG