Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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>
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