topic Join tables after using intervalmatch in QlikView

Join tables after using intervalmatch

Tue, 22 Jan 2013 15:48:08 GMT

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?

Re: Join tables after using intervalmatch

Anonymous — Tue, 22 Jan 2013 16:05:46 GMT

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

Re: Join tables after using intervalmatch

Wed, 23 Jan 2013 09:01:28 GMT

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.

Re: Join tables after using intervalmatch

er_mohit — Wed, 23 Jan 2013 09:28:48 GMT

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.