Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join the two tables in between two field values

Hello Folks, I am looking for solution how to join two tables in between field values.

TAB1:

IDMARKETIDEND_TIMESTART_TIMEVALUE
11640MPX268/14/2013 5:16:27 PM8/14/2013 11:02:45 AM22477
11642MPX268/14/2013 2:14:11 AM8/14/2013 12:47:01 AM5226

TAB2:

MARKETIDTIMEWONUMBERUSER
PX268/14/2013 1:06:27 PMP035687x-schanta
PX268/14/2013 2:06:47 AMP034676x-david

I want join these tables on MARKTID and TIME (TAB2) must be fall in b/n START_TIME and END_TIME (TAB1).

OUTPUT:

IDMARKETIDEND_TIMESTART_TIMEVALUEWONUMBERUSERTIME
11640PX268/14/2013 5:16:27 PM8/14/2013 11:02:45 AM22477P035687x-schanta8/14/2013 1:06:27 PM
11642PX268/14/2013 2:14:11 AM8/14/2013 12:47:01 AM5226P034676x-david8/14/2013 2:06:47 AM

Any thoughts, how to achieve this ?

Thanks in Advance...

5 Replies
Anonymous
Not applicable
Author

TAB1:

load * inline [

ID,MARKETID,VALUE

11640,MPX26,22477

11642,MPX26,5226

];

TAB11:

NoConcatenate

load ID,right(MARKETID,len(MARKETID)-1) as MARKETID,VALUE

resident TAB1;

Drop Table TAB1;

TAB2:

load * inline [

MARKETID,USER

PX26,P035687

PX26,P034676

];

Anonymous
Not applicable
Author

in terms of user please left(USER,len(USER)-2)

Not applicable
Author

Hi,

Please find the attached application

Not applicable
Author

Thanks Vikram. But I am working with very big tables.

Is there any way can I join in single load statement?

Gysbert_Wassenaar

You can use the intervalmatch function to match a value with an interval. See this blog post: IntervalMatch, and the document referenced at the end: IntervalMatch and Slowly Changing Dimensions


talk is cheap, supply exceeds demand