Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator 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
Highlighted
Master II
Master II

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.

Highlighted
Creator II
Creator II

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?

Highlighted
Master II
Master II

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;

 

Highlighted
Creator II
Creator II

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?

Highlighted
Master II
Master II

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?

Highlighted
Creator II
Creator II

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!