topic Re: Performance Issue in Left Join() in QlikView

Performance Issue in Left Join()

lawrance — Tue, 12 Dec 2017 19:57:11 GMT

Hi All,

I have two tables like TB_01 and TB_02 and have 60 million records and I want to perform left join but the execution takes more than 3 hours.

For Example,

TB_01:

Field_01,Field_02

60 millon records

Left Join(TB_01)

Filed_01,Field_03

3 million records

What is best practices to reduce the execution time and RAM Utilization.

Note: I have removed the duplicate records and unwanted fields.

Thanks,

Lawrance A

Re: Performance Issue in Left Join()

YoussefBelloum — Tue, 12 Dec 2017 20:29:06 GMT

Hi,

one of the best practices and best approaches to join is the Mapping table.

Read this:

Don't join - use Applymap instead

ApplyMap ‒ QlikView

PS: Mapping table and ApplyMap can't be used separately.

Re: Performance Issue in Left Join()

lawrance — Tue, 12 Dec 2017 20:39:45 GMT

Thanks for you response.

Have doubt on ApplyMap().

For Example,

TB_01:

Field_01	Field_02	Field_04
1	A	1
1	A	2
1	B	3
2	A	1
2	A	2

Left Join(TB_01)

Field_01	Field_03
1	A
1	B
2	A
2	B

How can I achieve ApplyMap() in above scenario?Is it possible to achieve above scenario?

Thanks,

Lawrance A

Re: Performance Issue in Left Join()

vishsaggi — Tue, 12 Dec 2017 20:46:12 GMT

What is final output you are expecting out of what you have posted.? May be do a QVD store and then run from QVDs to join.

Re: Performance Issue in Left Join()

lawrance — Tue, 12 Dec 2017 20:50:36 GMT

I expect the below results.

Field_01	Field_02	Field_03
1	A	A01
1	A	A01
1	B	B01
2	A	A01
2	B	B01

Note: I just rename the Field_03 column values from A to A01 but the actual value is A01.

Thanks,

Lawrance A

Re: Performance Issue in Left Join()

vishsaggi — Tue, 12 Dec 2017 21:12:56 GMT

How are you getting A01 in your third field? How are both table related any specific condition you are trying to use to convert value from A to A01?

Re: Performance Issue in Left Join()

YoussefBelloum — Tue, 12 Dec 2017 21:14:05 GMT

MapTB01:

Mapping Load

Field_01,

Field_02

From TB_01;

TB_02:

Load *,

ApplyMap('MapTB01', Field_01, null()) as Field_02
From TB_02 ;

Try it and tell me

Re: Performance Issue in Left Join()

marcus_sommer — Wed, 13 Dec 2017 09:43:04 GMT

It's not really clear what do you want to do. By a n:n relation between both tables you will by using a join always get duplicate records which is usually not wanted (whereby your output example showed a duplicate) and which could need a lot of performance. Further you will need some efforts to "clean" this result.

Therefore I would go with the suggestion from Youssef and using a mapping for it whereby I would probably aggregate the second table before with: concat(Field_03, '|'). These mapped results could be read with interrecord-functions like peek() or previous() and splitted with subfield() maybe within some if-loops to get the final data-structure. Even if this meant some efforts I would assume this approach a lot faster than joining and cleaning/preparing the results.

Beside this it might be worth to re-think the whole approach of merging these tables because there may better ways to create the datamodel.

- Marcus

Re: Performance Issue in Left Join()

michael_anywar — Thu, 22 Feb 2018 08:19:04 GMT

I have similar issues, only that mine

ID and Start have unique values so i need to make the values of Start Mapped to a New field (End), provided that ID belongs to the same Name in the next Row

ID	Start	End	Name
A	1	2	Name1
B	2	3	Name1
C	3		Name1
D	4	9	Name2
E	5	7	Name 3
F	9		Name 2
G	7	10	Name3
H	10		Name 3

I will be glad if i can some help on this