Adding complexity to Intervalmatch function

[#1]

Hi All,

I am building a P&L using a format spreadsheet.

ReportRowPL	Start2_4	End2_4
Operating Costs	400000	559999
Mine Administration	400000	409999
Mining	410000	419999
Ore Haulage	500000	509999
Ore Processing	555567	555567
Processing Penalty	5569	5569

I am using an intervalmatch to join the Report Row's back to the 'GLSegment2_4' (segment 2 and 4 of the GL account code).

match:
Intervalmatch (GLSegment2_4)
Load
Start2_4,
End2_4
resident PLRow;

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.

Thank you.

[johnw]

As a basic outline, here's how I might approach it.

1. Break your report row table into two tables. One table with only ranges, one mapping table with "match last 4".
2. Intervalmatch with the range table to establish ReportRowPL1.
3. Left join to yourself, adding field applymap('match last 4',right(GeneralLedger,4)) as ReportRowPL2.
4. Left join to yourself, adding field if(len(trim(ReportRowPL1)),ReportRowPL1,ReportRowPL2) as ReportRowPL.
5. 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.)

[#3]

Thanks for your help John, I'll let you know how I get on.

[#4]

Hi Matt,

I facing same issues that your facing. Can you please help me out if you can please.

http://community.qlik.com/forums/t/31125.aspx