Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hamza99a
Contributor II

Data Load

Hello All,

I currently am trying to create a data model that doesnt take too long. Currently, I extract data every day from SQL and I store it into a qvd with a date at the end so for example: NCINO_RELATIONSHIP2019-01-12 and then the next day it would be NCINO_RELATIONSHIP2019-01-13. I want to create one large table using these separate qvds. The only problem I get is that it takes very long and when it loads in Legal_Entities it loads in quick. However when I want to join relationship qvd's  it says its loading it into HAMZA_RECORDS-1. Is this creating another table or what is happening here?

 

This is my script:

HAMZA_RECORDS:
load distinct
PROD_DT as [Production Date],
[LLC_BI__Relationship_Type__c] as Relationship_Type
,[Relationship_Name_Formula__c] as Record_name
,[LLC_BI__Account__c] as AccountId
,Date(Floor([LastModifiedDate])) AS [LastModifiedDate]
//,[Name] as Account_Name
,[LLC_BI__Deposit__c] as Relationkey
,[LLC_BI__Loan__c] as LoanKey
,[LLC_BI__Borrower_Type__c] as Record_TYPE
from D:\QlikView\Production\Data\LEGAL_ENTITIES*.qvd(qvd);


left join (HAMZA_RECORDS)
LOAD distinct
//[Name]
Id as AccountId,
Date(Floor([LastModifiedDate])) as Account_Last_Modified_Date,
[LLC_BI__Tax_Identification_Number__c] as Record_TaxID
from D:\QlikView\Production\Data\NCINO_RELATIONSHIP*.qvd(qvd);

6 Replies
mwoolf
Honored Contributor II

Re: Data Load

I have two thoughts on this:

First, your QVD loads are not optimized: http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Second, maybe it would be quicker to concatenate load all of your NCINO_RELATIONSHIP qvds into a resident table and then join to the LEGAL_ENTITIES.

hamza99a
Contributor II

Re: Data Load

How would you optimize the qvd load? Also with concatenate could you give me an example? all the examples online include only 2 or 3 tables. How would I concatenate multiple qvd files?

mwoolf
Honored Contributor II

Re: Data Load

You can read the link about optimized loads.

Here is an example of concatenating the NCINO_RELATIONSHIP qvds:

NCINO_RELATIONSHIP_Temp:
LOAD distinct
//[Name]
Id as AccountId,
Date(Floor([LastModifiedDate])) as Account_Last_Modified_Date,
[LLC_BI__Tax_Identification_Number__c] as Record_TaxID
from D:\QlikView\Production\Data\NCINO_RELATIONSHIP*.qvd(qvd);

left join (HAMZA_RECORDS)
load
       *
resident NCINO_RELATIONSHIP_Temp;
drop table NCINO_RELATIONSHIP_Temp;

 

hamza99a
Contributor II

Re: Data Load

Okay, so after including that code it still seems to hang up at the relationship portion. It did concatenate as the number grew, but it is still getting stuck? Any ideas?

mwoolf
Honored Contributor II

Re: Data Load

When you say it is getting stuck, do you mean that it takes a long time but completes successfully?

Why do you want one large table as appose to two tables that are associatively joined?

hamza99a
Contributor II

Re: Data Load

I swtiched it so that it uses the resident and drop table feature in qlikview. However, two days of data takes way too long. I concatenated the tables and when I join to my 1 million rows, it takes very very long. I do not see qvd optimized when the load is in duration as well even though I didnt edit any of the columns when extracting. Is it possible that Qlikview is not fit to join to a million rows?

 

Thanks!