topic Re: Two records, delete one and store to qvd in QlikView

Two records, delete one and store to qvd

Wed, 26 Mar 2014 20:19:51 GMT

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.

Re: Two records, delete one and store to qvd

srchilukoori — Wed, 26 Mar 2014 20:25:53 GMT

check the attached app.

Re: Two records, delete one and store to qvd

srchilukoori — Wed, 26 Mar 2014 20:44:47 GMT

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;

Re: Two records, delete one and store to qvd

maxgro — Wed, 26 Mar 2014 20:46:48 GMT

directory;

//Q is your table with null values

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

load *

from Q.qvd (qvd)

where len(trim(Longitude))>0;

store T into T.qvd (qvd);

Re: Two records, delete one and store to qvd

Wed, 26 Mar 2014 21:08:58 GMT

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?

Re: Two records, delete one and store to qvd

maxgro — Wed, 26 Mar 2014 21:34:02 GMT

directory;

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)

;

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