Qlikview IntervalMatch if match is needed on a key and interval

[#1]

Could some one help with a sample script to  add  mgr and mgr_desc to output table - below is not working ...

tried loading transaction table and manager table

IMTable:

intervalmatch(trade_dt,acctid)

load *

resident manager;

left join (transaction)

load *

resident manager;

drop manager;

TRANSACTION_TABLE
ACCTID	TRADE_DT	TRADE_PROFIT
123	12/31/2012	25
123	6/15/2014	60
123	3/27/2015	32
234	2/1/2011	100
234	7/4/2016	300
456	1/10/2017	46
MANAGER_TABLE
ACCTID	MGR_START_DATE	MGR_END_DATE	MGR	MGR_DESC
123	9/2/2008	8/8/2013	SUN	SUNDEEP
123	8/9/2013	6/7/2015	SUD	SUDHEER
123	6/8/2015	2/25/2017	RAM	RAMYA
234	1/2/2009	2/3/2012	VIK	VIKRAM
234	2/4/2012	2/25/2017	SWE	SWETHA
456	8/25/2012	1/1/2017	VIJ	VIJAY
456	1/2/2017	2/25/2017	AKH	AKHILA
OUTPUT TABLE - TO ADD MGR AND MGR_DESC TO TRANSACTION TABLE
ACCTID	TRADE_DT	TRADE_PROFIT	MGR	MGR_DESC
123	12/31/2012	25	SUN	SUNDEEP
123	6/15/2014	60	SUD	SUDHEER
123	3/27/2015	32	SUD	SUDHEER
234	2/1/2011	100	VIK	VIKRAM
234	7/4/2016	300	SWE	SWETHA
456	1/10/2017	46	AKH	AKHILA

[sunny_talwar · Accepted solution]

This?

TRANSACTION_TABLE:

LOAD * INLINE [

    ACCTID, TRADE_DT, TRADE_PROFIT

    123, 12/31/2012, 25

    123, 6/15/2014, 60

    123, 3/27/2015, 32

    234, 2/1/2011, 100

    234, 7/4/2016, 300

    456, 1/10/2017, 46

];

MANAGER_TABLE:

LOAD * INLINE [

    ACCTID, MGR_START_DATE, MGR_END_DATE, MGR, MGR_DESC

    123, 9/2/2008, 8/8/2013, SUN, SUNDEEP

    123, 8/9/2013, 6/7/2015, SUD, SUDHEER

    123, 6/8/2015, 2/25/2017, RAM, RAMYA

    234, 1/2/2009, 2/3/2012, VIK, VIKRAM

    234, 2/4/2012, 2/25/2017, SWE, SWETHA

    456, 8/25/2012, 1/1/2017, VIJ, VIJAY

    456, 1/2/2017, 2/25/2017, AKH, AKHILA

];

Left Join (TRANSACTION_TABLE)

IntervalMatch (TRADE_DT, ACCTID)

LOAD MGR_START_DATE,

  MGR_END_DATE,

  ACCTID

Resident MANAGER_TABLE;

Left Join (TRANSACTION_TABLE)

LOAD *

Resident MANAGER_TABLE;

DROP Table MANAGER_TABLE;

[anushree1]

HI Sunny,

I happened to see the Interval Match with extended Syntax by the reply you have posted, so I just went and read about it in help and did a small check, I happen to see that Interval Match with extended Syntax does not give any synthetic keys unlike the usual Interval Match. Could you please explain the reason behind it. The script that I have used is as below:

TRANSACTION_TABLE:

LOAD * INLINE [

    ACCTID, TRADE_DT, TRADE_PROFIT

    123, 12/31/2012, 25

    123, 6/15/2014, 60

    123, 3/27/2015, 32

    234, 2/1/2011, 100

    234, 7/4/2016, 300

    456, 1/10/2017, 46

];

MANAGER_TABLE:

LOAD * INLINE [

    ACCTID, MGR_START_DATE, MGR_END_DATE, MGR, MGR_DESC

    123, 9/2/2008, 8/8/2013, SUN, SUNDEEP

    123, 8/9/2013, 6/7/2015, SUD, SUDHEER

    123, 6/8/2015, 2/25/2017, RAM, RAMYA

    234, 1/2/2009, 2/3/2012, VIK, VIKRAM

    234, 2/4/2012, 2/25/2017, SWE, SWETHA

    456, 8/25/2012, 1/1/2017, VIJ, VIJAY

    456, 1/2/2017, 2/25/2017, AKH, AKHILA

];

NoConcatenate

IntervalMatch:

IntervalMatch(TRADE_DT,ACCTID)

LOAD

MGR_START_DATE,

MGR_END_DATE

Resident MANAGER_TABLE;

[sunny_talwar]

I happen to see that Interval Match with extended Syntax does not give any synthetic keys unlike the usual Interval Match.

This is not true. Extended Interval Match will also give you a synthetic key. The correct code is this:

TRANSACTION_TABLE:

LOAD * INLINE [

    ACCTID, TRADE_DT, TRADE_PROFIT

    123, 12/31/2012, 25

    123, 6/15/2014, 60

    123, 3/27/2015, 32

    234, 2/1/2011, 100

    234, 7/4/2016, 300

    456, 1/10/2017, 46

];

MANAGER_TABLE:

LOAD * INLINE [

    ACCTID, MGR_START_DATE, MGR_END_DATE, MGR, MGR_DESC

    123, 9/2/2008, 8/8/2013, SUN, SUNDEEP

    123, 8/9/2013, 6/7/2015, SUD, SUDHEER

    123, 6/8/2015, 2/25/2017, RAM, RAMYA

    234, 1/2/2009, 2/3/2012, VIK, VIKRAM

    234, 2/4/2012, 2/25/2017, SWE, SWETHA

    456, 8/25/2012, 1/1/2017, VIJ, VIJAY

    456, 1/2/2017, 2/25/2017, AKH, AKHILA

];

IntervalMatch:

IntervalMatch(TRADE_DT,ACCTID)

LOAD MGR_START_DATE,

  MGR_END_DATE,

  ACCTID

Resident MANAGER_TABLE;

and I see the synthetic key

[anushree1]

cool thanks

[#6]

Thanks All for your inputs - was not using appropriate keys to join earlier.

Script below:

TRADEDETAILS:
LOAD date(TRADE_DT) as TRADE_DT,
     ACCTID,
     TRADE_PROFIT
FROM

(ooxml, embedded labels, table is TradeDetails);

MANAGER:
LOAD date(MGR_START_DATE) as MGR_START_DATE,
     date(MGR_END_DATE) as MGR_END_DATE,
     ACCTID, 
     MGR,
     MGR_DESC,
     autonumber(ACCTID & date(MGR_START_DATE)  & date(MGR_END_DATE)) as Key
FROM

(ooxml, embedded labels, table is ManageDetails);

TEMP:
IntervalMatch(TRADE_DT,ACCTID)
load
MGR_START_DATE,
MGR_END_DATE,
ACCTID
Resident MANAGER;

store TEMP into (txt);

Left join(TRADEDETAILS)
Load
TRADE_DT,
ACCTID,
AutoNumber(ACCTID & (MGR_START_DATE) & (MGR_END_DATE)) as Key
Resident TEMP;

drop table TEMP;


left join(TRADEDETAILS)
LOAD MGR,
     MGR_DESC,
     MGR_START_DATE,
     MGR_END_DATE,
     autonumber(ACCTID & date(MGR_START_DATE)  & date(MGR_END_DATE)) as Key
Resident MANAGER;

drop table MANAGER;

[sunny_talwar]

Great, if you got what you needed, please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny