topic to join or not to join, applymap, temptables. Optimisation in QlikView

to join or not to join, applymap, temptables. Optimisation

Mon, 09 May 2016 13:52:01 GMT

I have been reading a lot of different ways to help with performance of a reload.

I have come to conclusion that I will need to either applymap or join with the fact load rather than a left join outside of the resident load of the fact table.

My memory reached 40GB from n app that's less than 1GB when I reload from reading forums I have gathered that it's from all the left joins.

I have attached my script, could anyoen recommend what they would do with the script?

There's temp tables, joins, left joins and apply maps....

Thanks,

Lauren

Re: to join or not to join, applymap, temptables. Optimisation

Gysbert_Wassenaar — Mon, 09 May 2016 16:37:21 GMT

1. Don't join tables unless you need the fields from both tables to calculate new fields

Booking Agent

Transaction Agent

DimTransactionType

DimJourneyType

DimFareType

DimDates

2. Try storing Sales into a temporary qvd and do the final calculations with a load from this qvd instead of using a resident load.

3. If(SaleType=0,Company, ProductCompany) as CompanyOverall

ProductCompany doesn't seem to exist.

Re: to join or not to join, applymap, temptables. Optimisation

Tue, 10 May 2016 10:35:50 GMT

Hi Gysbert Wassenaar,

Thanks for the reply!

What would you suggest instead of joins for number 1?

For number 2. Please could you send an example of the script for this part or just a example structure of it? Would this all be in the script in the qvw?

Thanks again.

Re: to join or not to join, applymap, temptables. Optimisation

Gysbert_Wassenaar — Wed, 11 May 2016 05:35:48 GMT

1. Do nothing. Simply let Qlikview associate the tables using the fields they have in common.

2. Example:

Sales:

LOAD ...stuff ... FROM ...somewhere... ;

STORE Sales INTO Sales.qvd ;

DROP TABLE Sales;

SalesTransactions:

LOAD X, sum(Y) FROM Sales.qvd (qvd) GROUP BY X;

Re: to join or not to join, applymap, temptables. Optimisation

Wed, 11 May 2016 08:23:48 GMT

Hi,

Thanks for coming back to me.

For point one, just do this for each dim

LOAD [FlightKey] AS _FlightKey

,[ServiceNumber]

,[Company]

,[BrandCode]

,ServiceName

,[ServiceCategory]

FROM [..\QVD\DimServices.qvd] (qvd);

rather than

LEFT JOIN (Sales)

LOAD [FlightKey] AS _FlightKey

,[ServiceNumber]

,[Company]

,[BrandCode]

,ServiceName

,[ServiceCategory]

FROM [..\QVD\DimServices.qvd] (qvd);

I am bit confused as I believe I am already doing point 2.

As I am taking load from qvd:

FROM

[..\QVD\FactSalesTransactions.qvd] (qvd)

Re: to join or not to join, applymap, temptables. Optimisation

Gysbert_Wassenaar — Wed, 11 May 2016 10:51:44 GMT

At the end of your script you use a resident load:

SalesTransactions:
LOAD *,
    TopDepartureName &'-'&TopArrivalName AS Flow,
    If (TopDepartureName < TopArrivalName,
      TopDepartureName & '-' & TopArrivalName,
      TopArrivalName & '-' & TopDepartureName) AS FlowGrouped,
    If (DepartureName < ArrivalName,
DepartureName & '-' & ArrivalName,
ArrivalName & '-' & DepartureName) AS JourneyGrouped,
    DepartureName & '-' & ArrivalName AS Journey,
    If (LegFare = 0, 1, 0) AS ZeroFlag,
    If(SaleType=0,Company, ProductCompany) as CompanyOverall
Resident Sales;

DROP TABLE Sales;

You could store Sales into a qvd and drop that table first. Then instead of a resident load you could load data from the qvd. Perhaps that will perform better. It'll use less ram memory in any case.

Re: to join or not to join, applymap, temptables. Optimisation

Wed, 11 May 2016 12:04:18 GMT

Do you mean store the Fact with joined dimensions in a qvd?

and then call the qvd later in the script?

Re: to join or not to join, applymap, temptables. Optimisation

Anonymous — Wed, 11 May 2016 15:00:30 GMT

Instead of 'left join', have you tried to use a 'left keep' ? This reduces data in the same way a 'join' statement does, but QV keeps all tables. It is recommendable not to join.

Re: to join or not to join, applymap, temptables. Optimisation

Thu, 12 May 2016 12:18:40 GMT

At the end of the script I do some post load calculations on SalesTransactions. SalesTransactions would be the table that has the fact and dimension joined via left joins then at the end I use this to create fields from the dimensions:

How would I do this without joining please? Is this possible?

SalesTransactions:

LOAD *,

TopDepartureName &'-'&TopArrivalName AS Flow,

If (TopDepartureName < TopArrivalName,

TopDepartureName & '-' & TopArrivalName,

TopArrivalName & '-' & TopDepartureName) AS FlowGrouped,

If (DepartureName < ArrivalName,

DepartureName & '-' & ArrivalName,

ArrivalName & '-' & DepartureName) AS JourneyGrouped,

DepartureName & '-' & ArrivalName AS Journey,

If (LegFare = 0, 1, 0) AS ZeroFlag,

If(SaleType=0,Company, ProductCompany) as CompanyOverall

Resident Sales;

DROP TABLE Sales

Re: to join or not to join, applymap, temptables. Optimisation

Thu, 12 May 2016 12:18:49 GMT

How would I do this without joining please? Is this possible?

SalesTransactions:

LOAD *,

TopDepartureName &'-'&TopArrivalName AS Flow,

If (TopDepartureName < TopArrivalName,

TopDepartureName & '-' & TopArrivalName,

TopArrivalName & '-' & TopDepartureName) AS FlowGrouped,

If (DepartureName < ArrivalName,

DepartureName & '-' & ArrivalName,

ArrivalName & '-' & DepartureName) AS JourneyGrouped,

DepartureName & '-' & ArrivalName AS Journey,

If (LegFare = 0, 1, 0) AS ZeroFlag,

If(SaleType=0,Company, ProductCompany) as CompanyOverall

Resident Sales;

DROP TABLE Sales

Re: to join or not to join, applymap, temptables. Optimisation

Mon, 16 May 2016 10:14:13 GMT

Hi,

Please could you come back to me on this. My load is still over 2 hours long.

Re: to join or not to join, applymap, temptables. Optimisation

Gysbert_Wassenaar — Mon, 16 May 2016 16:17:08 GMT

Put statements like this around each load:

LET vStart = Now(1);

LOAD ...etc

LET vLoadTime = Interval(Now(1) - $(vStart),'hh:mm:ss');

TRACE $(vLoadTime);

Then check the log file for lines with these load times to check where the most time is spend.