topic Re: Data Load in QlikView

Data Load

hamza99a — Sat, 16 Nov 2024 04:37:17 GMT

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

Re: Data Load

m_woolf — Wed, 30 Jan 2019 13:33:53 GMT

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.

Re: Data Load

hamza99a — Wed, 30 Jan 2019 14:38:52 GMT

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?

Re: Data Load

m_woolf — Wed, 30 Jan 2019 14:50:20 GMT

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;

Re: Data Load

hamza99a — Wed, 30 Jan 2019 15:36:59 GMT

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?

Re: Data Load

m_woolf — Wed, 30 Jan 2019 15:41:06 GMT

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?

Re: Data Load

hamza99a — Thu, 31 Jan 2019 12:58:38 GMT

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!