Two records, delete one and store to qvd

[#1]

Hi Experts,

How do i get rid of the first row(row with null values, I want to retain only the ones with data) while storing it to a .qvd file?

Postal	Longitide	Latitude	Coordinates
600047	NULL	NULL	','
600047	Data	Data	Data,Data

Help appreciated.

Thank you.

[srchilukoori]

check the attached app.

[srchilukoori]

Try the following code:

Full:

LOAD Postal,

     Longitide,

     Latitude,

     Coordinates

FROM

[http://community.qlik.com/thread/112118]

(html, codepage is 1252, embedded labels, table is @1);

Data:

NoConcatenate

LOAD *

Resident Full

Where not isnull(Longitide)  and not isnull(Latitude);

DROP Table Full;

[maxgro]

directory;

//Q is your table with null values

Q:

load

Postal,

if(Longitude='NULL',null(),Longitude) as Longitude,

if(Latitude='NULL',null(),Latitude) as Latitude,

Coordinates;

load * inline [

Postal, Longitude, Latitude, Coordinates

600047, NULL, NULL, ','

600047, Data, Data, "Data,Data"

];

//Q is your qvd file with null values

store Q into Q.qvd (qvd);

drop table Q;

// how to get rid of null in Q?

// read with where filter

T:

load *

from Q.qvd (qvd)

where len(trim(Longitude))>0;

store T into T.qvd (qvd);

[#5]

Thank you guys. Let me add one more point that i missed. Sorry about that,

Data looks something like this...

I do not want to get rid of all the records with null values, but I want to get rid of the one postal record which has data for it in another record.

Or in another way how do i get the postal code that is occurring more than once in the table?

[maxgro · Accepted solution]

directory;

Q:

load

Postal,

if(Longitude='NULL',null(),Longitude) as Longitude,

if(Latitude='NULL',null(),Latitude) as Latitude,

Coordinates;

load * inline [

Postal, Longitude, Latitude, Coordinates

600047, NULL, NULL, ','

600047, Data, Data, "Data,Data"

600047, NULL, NULL, ','

1, NULL, NULL, ','

1, 1, 1, "a,b"

2, NULL, NULL, ','

3, Data, Data, "c,d"

];

store Q into Q.qvd (qvd);

drop table Q;

Tmp:

load *

from Q.qvd (qvd)

;

T:

NoConcatenate

load *

Resident Tmp

where Peek(Postal)<>Postal

order by Postal, Longitude desc;          // only first postal, desc because i wannt null at the end

DROP Table Tmp;

store T into T.qvd (qvd);