Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Adding complexity to Intervalmatch function

Hi All,

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

ReportRowPLStart2_4End2_4
Operating Costs400000559999
Mine Administration400000409999
Mining410000419999
Ore Haulage500000509999
Ore Processing555567555567
Processing Penalty55695569

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.

3 Replies
Highlighted
MVP
MVP

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.)

Highlighted
Not applicable

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

Highlighted
Not applicable

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