topic Time taken by resident load is huge in QlikView

Time taken by resident load is huge

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Time taken by resident load is huge

jaumecf23 — Thu, 29 Jun 2017 08:25:38 GMT

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

kaushiknsolanki — Thu, 29 Jun 2017 08:55:59 GMT

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

Re: Time taken by resident load is huge

marcus_malinow — Thu, 29 Jun 2017 09:17:56 GMT

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

Re: Time taken by resident load is huge

ananyaghosh — Thu, 29 Jun 2017 09:23:05 GMT

Hi,

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

Re: Time taken by resident load is huge

marcus_malinow — Thu, 29 Jun 2017 09:28:30 GMT

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];

Re: Time taken by resident load is huge

ananyaghosh — Thu, 29 Jun 2017 12:58:48 GMT

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?

Re: Time taken by resident load is huge

marcus_malinow — Thu, 29 Jun 2017 13:04:21 GMT

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

Would appreciate a 'helpful' here...