Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ananyaghosh
Creator III
Creator III

Time taken by resident load is huge

Hi,

The following tables are used in resident loads:

1. F_SALES_TRN -> have more that 29 lacks of rows

2. D_FIRM  -> have more that 2 lacks of rows

3. F_SALES_TRN_TERR_LIST  - have more that 29 lacks of rows

4. D_TERRITORY -> have more that 2 lacks of rows

and I have used left join between each table on a common column name like the below code:

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key"

resident [F_SALES_POSTING_DAY];

left join

LOAD

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

left join

load

   [Terr ID], 

    [Terr Regn Mgr Last Name],

    [Terr Regn Mgr First Name],

    [Manager Wholesaler]   

resident [D_TERRITORY];

left join

LOAD

"Firm ID",

"Firm Channel Desc"

resident [D_FIRM];

Now the problem is when I load my data model it takes huge amount time to load the data and number of rows are seems to be Cartesian join between the joining tables.

So do we have any solution that we can reduce the data load time as well as total number of rows?

Thanks,

Sandip Ghosh

7 Replies
jaumecf23
Creator III
Creator III

I think the problem could be in the first join that you do:

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key"

resident [F_SALES_POSTING_DAY];

left join

LOAD

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

...

It could be possible that "Sales Posting Day Key" and [Sales Posting Day Key] for Qlikview is not the same name and this is why you can do a Cartesian join between these tables ?

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

It is not the resident load which is taking time, but it is the join which is resource intensive.

Try below code.

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key"

resident [F_SALES_POSTING_DAY];

left join

LOAD Distinct

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

left join

load Distinct

   [Terr ID],

    [Terr Regn Mgr Last Name],

    [Terr Regn Mgr First Name],

    [Manager Wholesaler]  

resident [D_TERRITORY];

left join

LOAD Distinct

"Firm ID",

"Firm Channel Desc"

resident [D_FIRM];

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
marcus_malinow
Partner - Specialist III
Partner - Specialist III

A couple of comments:

1 - In order to determine where your bad join is happening, use NoOfRows-

LET vNoOfRows1 = NoOfRows('Temp Division');
TRACE $(vNoOfRows1);

2 - Use ApplyMap. If you are adding a single field in a join, and you are expecting only one value for each row in your original table, use ApplyMap instead of a JOIN

ananyaghosh
Creator III
Creator III
Author

Hi,

Can u use ApplyMap in my above joins? If so, please give me the code with ApplyMap function.

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Sandip,

if you don't already know how to use ApplyMap I would suggest reading up on it - Don't join - use Applymap instead

For your first join:

TerritoryMap:

MAPPING LOAD

[Sales Posting Day Key]

,[Terr ID]

resident [F_SALES_POSTING_DAY_TERR_LIST];

[Temp Division]:

load

"Firm ID",

"Sales Posting Day Key",

ApplyMap('TerritoryMap', "Sales Posting Day Key", Null()) as [Terr ID]

resident [F_SALES_POSTING_DAY];

ananyaghosh
Creator III
Creator III
Author

hi,

I have used the below script with Apply Map:

[Mapping 1]:
mapping load
"Firm ID",
"Trn Key"
resident [F_SALES_TRN];

[Mapping 2]:
mapping LOAD
[Trn Key]
,"F_SALES_TRN_TERR_LIST.Terr Key" as [Terr Key]
resident [F_SALES_TRN_TERR_LIST];

[Mapping 3]:
mapping
load 
  "D_TERRITORY.Terr Key" as [Terr Key],
  [Terr Regn Mgr Last Name] & [Terr Regn Mgr First Name] as Manager
resident [D_TERRITORY];

[Mapping 4]:
mapping
load 
  "D_TERRITORY.Terr Key" as [Terr Key],
  [Manager Wholesaler]
resident [D_TERRITORY];

[Mapping 5]:
mapping
load 
  "Firm ID",
  "Firm Channel Desc"
resident [D_FIRM];


[temp]:
load
ApplyMap('Mapping 1', 'Firm ID') as 'Trn Key',
ApplyMap('Mapping 2', 'Trn Key') as 'Terr Keys',
ApplyMap('Mapping 3', 'Terr Key') as 'Manager',
ApplyMap('Mapping 4', 'Terr Key') as 'Manager Wholesaler',
ApplyMap('Mapping 5', 'Firm ID') as 'Firm Channel Descs';

but [temp] table does not loaded in my data model. Any suggestion?

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Where are you loading [temp] from? You have no FROM or RESIDENT.

Would appreciate a 'helpful' here...