Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

autogenerate 0 with outer join

HI

I am left joining 3 tables as per below.   This bit works perfectly.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

MSCAR :

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

LOAD

  [Calculation MSA ID] as  [MSA ID]  ,

  *

FROM

'$(vSTWTFolderQVD)$(vDatabase).$(vOracleUser).STWT.MSC_Calculations.qvd'

(qvd);

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

left join ( [MSCAR] )

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

LOAD

  *

FROM

'$(vSTWTFolderQVD)$(vDatabase).$(vOracleUser).STWT.MSA_Approvals.qvd'

(qvd);

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

left join ( [MSCAR] )

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

LOAD

  [MSAR Environment ID]   as [MSA Environment ID MSAR]   ,

  *

FROM

'$(vSTWTFolderQVD)$(vDatabase).$(vOracleUser).STWT.MSAR_Approvals.qvd'

(qvd);

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

To this I need to add some dummy fields so that when it is stored later as a QVD, it will have the same fields as other QVD's.  Then in Dashboard qvw's these QVD's with identical field metadata, they can simply be concatenated together all with optimised loads.

In other QVD Generators I have done that with an MustInclude statement calling a script file that does AutoGenerate(0), but they are simply extracting from the databases and do have the joins above.

LOAD

Null()  as [STWT Fact Count] ,

Null()  as [STWT Employee Number] ,

date(Null())  as [STWT Date] ,

Null()  as [STWT Environment] ,

Null()  as [STWT Business Unit],

// There are a couple of hundred more fields in here

Null()  as  [Shift Year]

AutoGenerate(0)

;

If I place this at the beginning and concatenate the first table onto it, it takes ages and ages and ages to run.

If I place it at the end with an outer join, it improves a lot but for qvd's with a few million rows it takes a few minutes after the AutoGenerate(0).  I guess it is organising the resulting table out during this time.

I wish to use a communal script called via a MustInclude statement calling the script file that does the AutoGenerate(0) so that when extra fields are needed then just changing this single script will sort all the QVD Generators.

The whole thing is encased in a loop and done multiple times for multiple source databases which are used for different geographical areas.  So adding a few minutes run time per loop adds up to a significant addition to the overall run time

Any suggestion as to an efficient way to do this ?

Best Regards, Bill

7 Replies
whiteline
Master II
Master II

Hi.

As a simple solution you can try to store the structure into empty qvd.

Also depending on your requirements you can try to use Maps instead of joins.

Anonymous
Not applicable
Author

whiteline

Storing the structure into a empty qvd sounds interesting, but could you expand on how this would help ?

[Far too many fields for applymap() to be viable]

Best Regards, Bill

Not applicable
Author

Why don´t you create the empty fields during the first load step:

MSCAR :

LOAD

     null() as Field1,

     null() as Field2,

    

  [Calculation MSA ID] as  [MSA ID]  ,

  *

FROM

'$(vSTWTFolderQVD)$(vDatabase).$(vOracleUser).STWT.MSC_Calculations.qvd'

(qvd);

whiteline
Master II
Master II

It's just a guess that it works a little bit different with qvd and can affect performance.

Anonymous
Not applicable
Author

Fernando

I may well end up having to do as you suggest which will work, but it does not allow me to have a single communal qvs script file to autogenerate(0) the dummy fields and then re-use that script across multiple QVD generators.

You wouldn't happen to know what criteria are needed to ensure an optimised concatenated load ?

Best Regards,     Bill

Not applicable
Author

Hi Bill, if you are concatenating 2 QVD's, the second QVD must be unoptimized load. For example,

MAIN:

Load A , B , C Autogenerate (0) ;

Concatenate (MAIN)

LOAD * FROM QVDNAME.qvd (qvd) ;

In the above case the QVD must be unoptimized load because you can concatenate this QVD to MAIN table.

So better performance , Always Load big qvd at first place.

You are creating the some Dummy fields with Autogenerate. If these fields already exist in any othe qvd's, you can create with dummy table directly from qvd with forced unequal condition.

LOAD A , B , C FROM QVDNAME.qvd (qvd) where 1<>1 ;

The above statement create a dummy table with fields A,B,C with no data.

So you dont need Autogenerate script, you can directly load from the qvd if the fields exist in the qvd's.

Not applicable
Author

For optimized load you must not rename the fields nor use any "WHERE" clasuses.

Regards,

Fernando