Join tables after using intervalmatch

[#1]

I have two tables

Accounts:

AccountNumber	Amount
160000	€ 500
162000	€ 500
172000	€ 1000
172100	€ 2000
172200	€ 2000
172201	€ 1000

Totals:

TotalNumber	Amount	Begin	End
1	€ 7000	100000	199999
16	€ 1000	160000	169999
17	€ 6000	170000	179999

I used the intervalmatch fuction to calculate the amount of the Totals (to calculate total 1 I have to begin with the amount of AccountNumber 100000 and end with account number 199999, this was the function:

INTERVALMATCH (AccountNumber) LOAD

     Begin,

     End

RESIDENT Totals;        

Now I want both table Accounts and table Totals in one table.

I have tried:

NoConcatenate LOAD * Resident Accounts;

Inner Join LOAD * Resident Totals;

DROP Table Accounts;

DROP Table Totals;

But this gave very weird results. Is there any way to combine them?

[Anonymous]

Try this out:

Accounts:
LOAD
     AccountNumber,
     AccountNumber AS [AccountNumber ReportLink]
     AccountName
FROM Account.xls (biff, embedded labels, table is [Accounts$]);
ReportGroups:
LOAD
     RowNo() AS RGKey,
     Begin, 
     End,
     Name
FROM ReportDefinitions.xls (biff, embedded labels, table is [RepDef$]);
TempReportsLink:
INTERVALMATCH (AccountNumber)
LOAD
    Begin,
    End
RESIDENT ReportGroups;
LEFT JOIN (ReportGroups) LOAD
     RGKey,
     Begin&'-'&End AS ReportsLinkKey
RESIDENT ReportGroups;
ReportsLink:
LOAD
     Begin&'-'&End AS ReportsLinkKey,
     AccountNumber AS [AccountNumber ReportLink]
RESIDENT TempReportsLink;
DROP TABLE TempReportsLink;

Jonathan

[#3]

thx for your reply. I still don't get them in one table, I actually want a table or chart in qlikview like this :

Accounts:

AccountNumber	Amount
1	€ 7000
16	€ 1000
160000	€ 500
162000	€ 500
17	€ 6000
172000	€ 1000
172100	€ 2000
172200	€ 2000
172201	€ 1000

I still have the problem that for example account no 160000 is in group 1 and 16 aswell so a pivot table gives doubles.

[er_mohit]

try this

Account:

mapping LOAD AccountNumber,

     Amount

FROM

(ooxml, embedded labels, table is Sheet1);

Total:

LOAD TotalNumber,

     Amount,

     Begin,

     End,

     ApplyMap('Account',Amount)as AccountNumber

FROM

(ooxml, embedded labels, table is Sheet2);

i have attached the file see this.