Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
lawrance
Contributor II

Performance Issue in Left Join()

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

Tags (2)
8 Replies
YoussefBelloum
Esteemed Contributor

Re: Performance Issue in Left Join()

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
Contributor II

Re: Performance Issue in Left Join()

Thanks for you response.

Have doubt on ApplyMap().

For Example,

TB_01:

Field_01Field_02Field_04
1A1
1A2
1B3
2A1
2A2

Left Join(TB_01)

Field_01Field_03
1A
1B
2A
2B

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

Thanks,

Lawrance A

vishsaggi
Esteemed Contributor III

Re: Performance Issue in Left Join()

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
Contributor II

Re: Performance Issue in Left Join()

I expect the below results.

Field_01Field_02Field_03
1AA01
1AA01
1BB01
2AA01
2BB01

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

Thanks,

Lawrance A

vishsaggi
Esteemed Contributor III

Re: Performance Issue in Left Join()

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
Esteemed Contributor

Re: Performance Issue in Left Join()

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

MVP & Luminary
MVP & Luminary

Re: Performance Issue in Left Join()

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
Contributor

Re: Performance Issue in Left Join()

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

IDStartEndName
A12Name1
B23Name1
C3Name1
D49Name2
E57Name 3
F9Name 2
G710Name3
H10Name 3

I will be glad if i can some help on this