Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

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
Honored Contributor II

Re: autogenerate 0 with outer join

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.

Re: autogenerate 0 with outer join

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

Re: autogenerate 0 with outer join

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
Honored Contributor II

Re: autogenerate 0 with outer join

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

Re: autogenerate 0 with outer join

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

Re: autogenerate 0 with outer join

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

Re: autogenerate 0 with outer join

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

Regards,

Fernando

Community Browser