Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Two records, delete one and store to qvd

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?

PostalLongitideLatitude
Coordinates
600047NULLNULL','
600047DataDataData,Data

Help appreciated.

Thank you.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

View solution in original post

5 Replies
srchilukoori
Specialist
Specialist

check the attached app.

srchilukoori
Specialist
Specialist

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
MVP
MVP

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

Not applicable
Author

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

Data looks something like this...

Capture.JPG.jpg

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
MVP
MVP

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