Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (2)
5 Replies
anutosh_dhar
Contributor

Re: Join the two tables in between two field values

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

];

anutosh_dhar
Contributor

Re: Join the two tables in between two field values

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

Not applicable

Re: Join the two tables in between two field values

Hi,

Please find the attached application

Not applicable

Re: Join the two tables in between two field values

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

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

Re: Join the two tables in between two field values

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
Community Browser