Intervalmatch on several fields with AND operation?

[Amphan]

I am trying to figure out how to categorize a transaction depending on whether several different fields in the transaction are within a certain range. In my current task I have seven fields, let's call them ACCODE01 - 07 and seven corresponding ranges, ACCODE01_START, ACCODE01_END, ACCODE02_START, ACCODE02_END etc. If each ACCODEXX field is within its corresponding range, ie. ACCODE01 is in ACCODE01_START to ACCODE01_END AND ACCODE02 is in ACCODE02_START to ACCODE02_END AND etc. then I want to mark the transaction with a Category.

Let's say I have these tables (random data, might not add up), what I want is to add CATEGORY from INTERVAL: to FACT: where ACCODEXX is in THE ACCODEXX_START to ACCODEXX_END range. If there is no match, no category should be assigned.

FACT:
LOAD * INLINE [
    ACCODE01, ACCODE02, ACCODE03, ACCODE04, ACCODE05, ACCODE06, ACCODE07, VERID, AMOUNT
    10000, 40261, 12340, 12300, 5478, 2541, 2587, 123456789, $100
    20000, 86210, 43210, 45600, 9866, 2365, 1236, 987546, $1000
    30000, 86400, 58740, 78900, 2222, 5895, 9854, 124578, $200
    40000, 86111, 98560, 98700, 9874, 4578, 7854, 326598, $1100
    50000, 46435, 12450, 65400, 2587, 1245, 2569, 785612, $800
    60000, 45698, 45780, 32100, 3698, 6542, 3254, 324578, $500
    70000, 32145, 65540, 74100, 1478, 1111, 1256, 1245782, $600
    80000, 78954, 32120, 36900, 2589, 2224, 9854, 69854225, $1900
];

INTERVAL:
LOAD * INLINE [
    CATEGORY, ACCODE01_START, ACCODE01_END, ACCODE02_START, ACCODE02_END, ACCODE03_START, ACCODE03_END, ACCODE04_START, ACCODE04_END, ACCODE05_START, ACCODE05_END, ACCODE06_START, ACCODE06_END, ACCODE07_START, ACCODE07_END
    APPLES, 0000, 8999, 40260, 40270, 00000, 96999, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 0000
    ORANGES, 0000, 8999, 65410, 65510, 00000, 96999, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 0000
    BANANAS, 0000, 8999, 86110, 86710, 03560, 03560, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 9999
    CHERRYS, 0000, 8999, 86310, 86330, 03560, 03560, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 9999
    LIMES, 0000, 8999, 86400, 86400, 03560, 03560, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 9999
    LEMONS, 0000, 8999, 46430, 46440, 00000, 96999, 1000, 1099, 0000, 9999, 0000, 9999, 0000, 0000
];

I'd really appreciate any help to move forward on this task.

Thanks in advance!

Ola

[#2]

Ola,

Please do one thing for me. Try to execute your script without all

statements after this join and let me know how looks this joined table. Let

me know.

Regards

D

16-05-2014 11:18 użytkownik "Ola Paulsson" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Intervalmatch on

several fields with AND operation?

reply from Ola Paulsson<http://community.qlik.com/people/OlaPaulsson?et=watches.email.thread>in

Scripting - View the full discussion<http://community.qlik.com/message/529728?et=watches.email.thread#529728>

[#3]

Ok, i'am back in my office. I tryed to do that join. It causes cartesian. In tmp_bridge you have FACT numbe rows x Interval number rows. Of course it is your choice, but my reccomendation is to go rather step by step (key by key) if you have bigger data volumes. I feel, that it will work faster.

Good Luck!

Darek