ELIMINATE NULL ROWS

[#1]

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.

[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;

[#3]

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

[Anil_Babu_Samineni]

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

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

[#6]

Below is the expected result

58484	356541
58495	356541
58848	356541
59794	356541
59938	356541
60322	356541
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?

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