My problem is that not all of the report row's are categorised on segment 2 and 4 of the account code.
In the spreadsheet above, you will see that 'Processing Penalty' has only 4 characters. It needs to exist for all segment 2 combinations where the segment 4 matches to 5569.
Ideally Qlikview would read * as an automatic wildcard and as a result I could use **5569 and **5569 as a wildcard intervalmatch, but clearly this is not the case. Is there a method I can use which achieves the same result with minimal script or spreadsheet input complexity?
I'd love to hear your thoughts on how I can solve this problem.
As a basic outline, here's how I might approach it.
Break your report row table into two tables. One table with only ranges, one mapping table with "match last 4".
Intervalmatch with the range table to establish ReportRowPL1.
Left join to yourself, adding field applymap('match last 4',right(GeneralLedger,4)) as ReportRowPL2.
Left join to yourself, adding field if(len(trim(ReportRowPL1)),ReportRowPL1,ReportRowPL2) as ReportRowPL.
Drop all the fields and tables you no longer need.
That's assuming there's no specific sequence to the rows in the report row table.
(EDIT: Ah, I seem to have missed that a general ledger code can have more than one report row, in which case you want ALL of the report rows that match. So a 405569 general ledger code should be in Operating Costs, Mine Administration, AND Processing Penalty. I'm not picking up the Processing Penalty in the rough outline above.)