topic Re: How to remove garbage rows in this scenario? in QlikView

How to remove garbage rows in this scenario?

Anonymous — Fri, 25 Apr 2014 11:25:00 GMT

Here we can see name of the cities are repeating...For my scenario the name of the city arising for the first time is valid otherwise consider it as garbage value.Like 'Chak Forty M' is again repeating in 2nd and 3rd row, so here I want only 1st row to be loaded and 2nd & 3rd should avoided or deleted...How to achieve the same???

Thanks in Advance

Regards

Himanshu Kumar

Re: How to remove garbage rows in this scenario?

kushalthakral — Fri, 25 Apr 2014 11:27:12 GMT

Hi Himanshu

Can you share this in excel or share your qvw

Regards

Kushal

Re: How to remove garbage rows in this scenario?

Anonymous — Fri, 25 Apr 2014 11:30:10 GMT

Hi Kushal,

I have attached excel sheet.Please find attachment.

Re: How to remove garbage rows in this scenario?

kushalthakral — Fri, 25 Apr 2014 11:34:01 GMT

can you try FirstSortedValue in expression

Re: How to remove garbage rows in this scenario?

kushalthakral — Fri, 25 Apr 2014 11:36:46 GMT

check out this link

http://community.qlik.com/thread/30532

Re: How to remove garbage rows in this scenario?

Anonymous — Fri, 25 Apr 2014 11:36:59 GMT

Hi,

How will you remove garbage rows at scripting level????

Re: How to remove garbage rows in this scenario?

Fri, 25 Apr 2014 11:50:58 GMT

Hi,

you can use "Group By City1" clause in your load script. with max (Latitude) and max(Longitude).

Re: Re: How to remove garbage rows in this scenario?

fernando_tonial — Fri, 25 Apr 2014 11:51:50 GMT

Hi, You try AVG

LOAD
City1,
Avg(Latitude) as Latitude
Avg(Longitude) as Longitude
From Testing.xls
Group By City1;

Best Regards.

Tonial.

Re: How to remove garbage rows in this scenario?

Anonymous — Fri, 25 Apr 2014 12:01:08 GMT

thanks a lot Kushal,m_sheraz_alam and Fernando for your efforts....As per my scenario I am using FirstValue function in my expression.

Re: How to remove garbage rows in this scenario?

anbu1984 — Sat, 26 Apr 2014 08:04:09 GMT

If you want first row for each City, try this.

LOAD City1 ,Latitude, Longitude

FROM

(biff, embedded labels)

where City1 <> Previous(City1);

Re: Re: How to remove garbage rows in this scenario?

MarcoWedel — Sun, 27 Apr 2014 22:33:38 GMT

you're right, FirstValue solves your problem:

tabCities:
LOAD City1,
    FirstValue(Latitude) as Latitude,
    FirstValue(Longitude) as Longitude
FROM [http://community.qlik.com/servlet/JiveServlet/download/516020-103612/Testinng.xls]
(biff, embedded labels, table is Sheet1$)
Group By City1;

Did I understand correct, that you found the solution on your own and do not have any other issues, or are there any questions left?

regards

Marco

Re: How to remove garbage rows in this scenario?

disqr_rm — Mon, 28 Apr 2014 01:56:52 GMT

LOAD City, Latitude, Longitude FROM\Whatever.xlx.. WHERE Not Exists (City);