Data Load

[hamza99a]

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

[m_woolf]

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]

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?

[m_woolf]

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]

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?

[m_woolf]

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]

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!