Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qlikview IntervalMatch if match is needed on a key and interval

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
ACCTIDTRADE_DTTRADE_PROFIT
12312/31/201225
1236/15/201460
1233/27/201532
2342/1/2011100
2347/4/2016300
4561/10/201746
MANAGER_TABLE
ACCTIDMGR_START_DATEMGR_END_DATEMGRMGR_DESC
1239/2/20088/8/2013SUNSUNDEEP
1238/9/20136/7/2015SUDSUDHEER
1236/8/20152/25/2017RAMRAMYA
2341/2/20092/3/2012VIKVIKRAM
2342/4/20122/25/2017SWESWETHA
4568/25/20121/1/2017VIJVIJAY
4561/2/20172/25/2017AKHAKHILA
OUTPUT TABLE - TO ADD MGR AND MGR_DESC TO TRANSACTION TABLE
ACCTIDTRADE_DTTRADE_PROFITMGRMGR_DESC
12312/31/201225SUNSUNDEEP
1236/15/201460SUDSUDHEER
1233/27/201532SUDSUDHEER
2342/1/2011100VIKVIKRAM
2347/4/2016300SWESWETHA
4561/10/201746AKHAKHILA
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

6 Replies
sunny_talwar

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
Specialist II
Specialist II

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

Capture.PNG

anushree1
Specialist II
Specialist II

cool thanks

Not applicable
Author

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