Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
ananyaghosh
Contributor 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

Tags (1)
7 Replies
jaumecf23
Contributor III

Re: Time taken by resident load is huge

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 ?

Re: Time taken by resident load is huge

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

marcus_malinow
Valued Contributor III

Re: Time taken by resident load is huge

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

Re: Time taken by resident load is huge

Hi,

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

marcus_malinow
Valued Contributor III

Re: Time taken by resident load is huge

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

Re: Time taken by resident load is huge

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
Valued Contributor III

Re: Time taken by resident load is huge

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

Would appreciate a 'helpful' here...