How to get rid off records you don't need from a table that is linked by Intervalmatch

[hectorgarcia]

I have a Big table (almost 7 million records with information about geo IP. The manage 2 fields startIpNumand end endIpNum and this is numeric.)

ip2location:

Load

[Beginning IP Number] as startIpNum

,

[Ending IP Number]

as endIpNum

,

[Country Code]

as country

,

[Country Code]

as countryflag

,

[Country Name]

as COUNTRY

,

Region

,

City

,

Latitude

as latitude

,

Longitude

as longitude

,

ISP

,

Domain

FROM

.. \DataSources\QVDs\Commonfiles\Geoip\ip.qvd (qvd)

;

In the fact table, the field IP is in IP format so the only way to establish a relation with the geo ip table is :

1) Converting the ip number to a numeric value usin:

load *,

subfield(USERIP,'.',1)*(256*256*256)+subfield(USERIP,'.',2)*(256*256)+subfield(USERIP,'.',3)*(256)+subfield(USERIP,'.',4) as numip

FROM

.. \DataSources\QVDs\BCO\Bolp\HIST\$(date)_eventosbco.qvd (qvd)

where

EXISTS(SESSIONID1,SESSIONID);

2) using intervalmatch

intervalmatch

(numip) load startIpNum, endIpNum resident ip2location;

Most of the times I use 35000 records of the ip2location table. So there is more tnan 6 million records I do not need and I do not know how to get rid off to improve my application performance

any help will be more than welcome

[yblake]

You can left join data retrieved (Location) thru intervalmatch, like (not sure of exact syntax) :

left Join (sessions) IntervalMatch (numip) Load StartIpNum, EndIpNum,Location resident ip2Location;

then drop ip2Location which is no more needed.

[rwunderlich · Accepted solution]

Just to clarify Yves suggestion. Simple IntervalMatch only takes two fields in the load. So you'll have to use two joins to get all the fields into the sessions table before you drop ip2Location. Like this:

LEFT JOIN (sessions) IntervalMatch (numip) LOAD StartIpNum, EndIpNum RESIDENT ip2Location;
// Join all the other fields
LEFT JOB (sessions) LOAD DISTINCT * RESIDENT ip2Location;
DROP TABLE ip2Location;

-Rob

[hectorgarcia]

thanks guys , it worked but...partially, since I lost the other fields from the table I dropped (country name) , ISP name , and other key attributes that are the main reason from the intervalmatch.

How can I retrieve the other fields form the table i'm using the intervalmatch function?

[hectorgarcia]

Guys I thinf I found the solution (not sure if is the best but it works).

1)I created a key composed by startip&endip in the ip2location table

2)I applied the solution you provided (left join intervalmatch

3) I created the same key in the fact table startip&endip

4) I left joined by the filed key already in teh 2 tables and got the rest of the info I needed.

5) I dropped the table ip2location and the fileds I do not needed (key , starip endip)

Thanks for your help