topic Re: Please help! - Inner join preventing QVD creation? in QlikView

Please help! - Inner join preventing QVD creation?

Tue, 21 Apr 2015 08:27:27 GMT

Hi,

Please could someone pass their eyes over this... I'm getting some strange behaviour.

The below, aims to make a virtual table (TEMPDATA) from 3 tables prevously loaded in the Load Script, and uses the Resident command.

Once teh TEMPDATA has been created, it then exports teh table as a QVD file.

I've commented out the last table inner join... When I remove it, the code runs fine and creates the QVD with good data.

When I include that table, the code runs, but the output is only a QVD file with headers only. It seems to cause a conflict.

Can anyone see why?

Many thanks in advance for your help.

Kind Regards

Matt

TEMPDATA:

NoConcatenate
LOAD [Gen Nom Group],[Asset_IDA],[Gen CntryA],[Bnd Eq Indicator]
Resident GENG_NOMSUB_ASSET;

Inner Join (TEMPDATA)
LOAD [Gen Nom Group],[Security_AcS],[Gen NomE],[Gen_SubE],[NomineeKey],[Bnd Eq Indicator]
Resident GENG_NOMSUB_SECAC_E;
/*
Inner Join (TEMPDATA)
LOAD [Gen Nom Group],[Security_AcS],[Gen NomG],[Gen_SubG],[NomineeKey],[Bnd Eq Indicator]
Resident GENG_NOMSUB_SECAC_G;
*/
STORE TEMPDATA into 3 - MATRIX_GEN_SSI.QVD (QVD);

Re: Please help! - Inner join preventing QVD creation?

pokassov — Tue, 21 Apr 2015 08:31:18 GMT

Hi!

You can comment last line from your script and uncomment second inner join.

I think the problem is your data in the GENG_NOMSUB_SECAC_E is different from previous tables.

so table1*table2*table3=0 rows.

If you want sum rows from your tables you need to use concatenate (TEMPDATA) for second and third resident load.

TEMPDATA

NoConcatenate

Load ...

concatenate (TEMPDATA)

load ...

concatenate (TEMPDATA)

load ...

Re: Please help! - Inner join preventing QVD creation?

Tue, 21 Apr 2015 08:46:23 GMT

Thank you for picking up my query.

I'm a little unclear on this. Can I perhaps re-word.

If I load all 3 tables into Qlik, and let the standard Qlik join occur simply on column names e.g [Gen Nom Group] I can create a report (Table Box) bringing and combining the data from all 3 sources to create a single final table. The problem for me is that due to the amount of data in the tables, it cant process it on-screen (millions of rows) so I need to replicate it in code and output the result.

Am I going about this the right way?

Many thanks...
Matt

Re: Please help! - Inner join preventing QVD creation?

pokassov — Tue, 21 Apr 2015 09:01:09 GMT

Ok. I forget about concatenate...

I see four fields with the sames names for 2 and 3 tables.

My guess is the inner join between 2 and 3 tables gives you 0 rows interselection.

[Gen Nom Group],

[Security_AcS],

[NomineeKey],

[Bnd Eq Indicator]

Re: Please help! - Inner join preventing QVD creation?

Tue, 21 Apr 2015 09:19:14 GMT

Thank you... I'll look down that avenue.

I suspect you are right.. I'll update once I've investigated.

Many thanks for taking the time to advise.

Kind Regards

Matt