Performance Issue in Left Join()

[lawrance]

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

[YoussefBelloum]

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.

[lawrance]

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

[vishsaggi]

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.

[lawrance]

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

[vishsaggi]

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?

[YoussefBelloum]

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

[marcus_sommer]

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

[michael_anywar]

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