topic Re: Connect 2 tables in QlikView

Connect 2 tables

Anonymous — Fri, 02 Sep 2016 09:52:32 GMT

Dear Gurus,

I've the following problem:

Two table with the following values:

Table A:

ID	Group	First ID2	Last ID2	Descriprion
70000	SP330SP360	0602000001	0602000001	ALTRI CREDITI
70000	SP330SP360	0602000015	0602000015	ALTRI CREDITI
70000	SP330SP360	0602000017	0602000017	ALTRI CREDITI
70000	SP330SP360	0602000020	0602000023	ALTRI CREDITI
90000	SP350	0604000002	0604000100	EFFETTI ATTIVI IN PORTAFOGLIO
95000	SP330	0604000002	0604000011	CREDITI VERSO SOCIETA DI PROPRIETA'

Table B:

ID2	Descriprion2	Amount
0602000001	…	+1236,55
0602000002	…	+1244,00
0602000003	…	+123,56
0602000004	…	-125,15
0602000005	…	-12,18
0602000006	…	+13985,87
0602000007	…	0
0602000008	…	0
0602000009	…	0
0602000010	…	0
0602000011	…	0
0602000012	…	0
0602000013	…	0
0602000014	…	0
0602000015	…	0
0602000016	…	0
0602000017	…	0
0602000018	…	0
0602000019	…	0
0602000020	…	0
0602000021	…	0
0602000022	…	0
0602000023	…	0
0602000024	…	0

End now the poroblem:

In the table A i've only the first ID2 and the last ID2. Are there any way to connect this two tables?

And the second problem is that ID2 can be contained in more than one row of Table A like the row 5 and 6.

Sameone can help me?

Thanks.

Nicola.

Re: Connect 2 tables

effinty2112 — Sat, 03 Sep 2016 11:14:10 GMT

Hi Nicola,

An IntervalMatch in your load script will do what you need. Add this after Tables A & B are loaded:

IntervalMatch:

IntervalMatch(ID2) LOAD [First ID2],[Last ID2] Resident TableA;

This will give you a data model with a synthetic key. DON'T WORRY. Unexpected synthetic keys in your data model are often a sign of a defect in your script and they may cause performance problems. This is not the case when a synthetic key is created by the Intervalmatch function - this is normal and you need not add lines to your script to remove it. It's fine.

This is the result shown in a tablebox:

ID2	Descriprion2	Amount	Group	ID	Descriprion
0602000001	…	+1236,55	SP330SP360	70000	ALTRI CREDITI
0602000002	…	+1244,00
0602000003	…	+123,56
0602000004	…	-125,15
0602000005	…	-12,18
0602000006	…	+13985,87
0602000007	…	0
0602000008	…	0
0602000009	…	0
0602000010	…	0
0602000011	…	0
0602000012	…	0
0602000013	…	0
0602000014	…	0
0602000015	…	0	SP330SP360	70000	ALTRI CREDITI
0602000016	…	0
0602000017	…	0	SP330SP360	70000	ALTRI CREDITI
0602000018	…	0
0602000019	…	0
0602000020	…	0	SP330SP360	70000	ALTRI CREDITI
0602000021	…	0	SP330SP360	70000	ALTRI CREDITI
0602000022	…	0	SP330SP360	70000	ALTRI CREDITI
0602000023	…	0	SP330SP360	70000	ALTRI CREDITI
0602000024	…	0
			SP330	95000	CREDITI VERSO SOCIETA DI PROPRIETA'
			SP350	90000	EFFETTI ATTIVI IN PORTAFOGLIO

Kind regards

Andrew

Re: Connect 2 tables

effinty2112 — Sat, 03 Sep 2016 11:24:03 GMT

Hi Nicola,

Regarding the last question in your post:

And the second problem is that ID2 can be contained in more than one row of Table A like the row 5 and 6.

ID	Group	First ID2	Last ID2	Descriprion
90000	SP350	0604000002	0604000100	EFFETTI ATTIVI IN PORTAFOGLIO
95000	SP330	0604000002	0604000011	CREDITI VERSO SOCIETA DI PROPRIETA'

This is a problem with the source data I would suggest. If TableB has a record with ID2 in the range 0604000002 - 0604000011 it is not at all clear what the associated ID, Group or Description should be for such a record. Is it possible that [First ID2] for ID 90000 should read 0604000012 ?

Kind regards

Andrew

Re: Connect 2 tables

Anonymous — Fri, 14 Oct 2016 07:48:36 GMT

Thankyou Andrew.

with the IntervalMatch in load script I've solved my problem.

I've resolvedthe second problem too.