topic Re: Synthetic Keys not created automatically in QlikView

Synthetic Keys not created automatically

Tue, 03 Jun 2014 12:44:04 GMT

People, I started working with Qlikview last week, and there's my problem bellow.

I have to load 2 tables with finance information, and they have 3 columns in common. When I load, give me an error: 'Execution of Script Failed. Reload old data?'

Check the statements bellow:

SQL SELECT

MONTHY,

QUARTER,

YEAR

FROM "DBM_MKT".dbo."TIME";

SQL SELECT

PK_AGENCY,

NAME_AGENCY,

DISTRICT,

SR,

SUAT,

REGION,

WORKERID,

NAME_WORKER

FROM "DBM_MKT".dbo."GEO"

WHERE SUAT LIKE 'AREA%';

SQL SELECT

PK_AGENCY,

MONTHY,

PRODUCT,

TRANSACTION,

QUALITYSELL,

AMOUNT

FROM "DBM_MKT".dbo."QUALITY_SELL";

SQL SELECT

PK_AGENCY,

MONTHY,

PRODUCT,

SUMVALUE

FROM "DBM_MKT".dbo."SELL_VALUE";

Someone can help me?

Re: Synthetic Keys not created automatically

tresB — Tue, 03 Jun 2014 12:47:48 GMT

Synthetic keys with large data can cause the issue. Try to reload with Debug mode -> Limited load, does the issue persist?

Re: Synthetic Keys not created automatically

Tue, 03 Jun 2014 12:50:04 GMT

It doesn't help. The issue remains. Thanks

Re: Synthetic Keys not created automatically

tresB — Tue, 03 Jun 2014 12:51:37 GMT

Loading single table?

Re: Synthetic Keys not created automatically

Tue, 03 Jun 2014 12:54:46 GMT

I reload on Debug mode and all tables were read and the script finished. But when I click 'Close' the message 'Execution of Script Failed. Reload old data?' appears again.

Re: Synthetic Keys not created automatically

tresB — Tue, 03 Jun 2014 12:59:40 GMT

I would reload the first table and see, if works, then the first two together and so. That would give you a better picture of the issue.

Re: Synthetic Keys not created automatically

Tue, 03 Jun 2014 13:04:17 GMT

I tried to load each table alone and it is okay, the problem is when I load QUALITY_SELL and SELL_VALUE together.

Re: Synthetic Keys not created automatically

jonathandienst — Tue, 03 Jun 2014 13:10:01 GMT

The usual solution for this is to concatenate the tables. It's not a problem that the fields are not identical - QV is very efficient with sparse tables.

Sell:

SQL SELECT

PK_AGENCY,

MONTHY,

PRODUCT,

TRANSACTION,

QUALITYSELL,

AMOUNT,

'QUALITY_SELL' As Source

FROM "DBM_MKT".dbo."QUALITY_SELL";

Concatenate(Sell)

SQL SELECT

PK_AGENCY,

MONTHY,

PRODUCT,

SUMVALUE,

'SELL_VALUE' As Source

FROM "DBM_MKT".dbo."SELL_VALUE";

You can select data from one or the other table using the Source field.

HTH

Jonathan

Re: Synthetic Keys not created automatically

tresB — Tue, 03 Jun 2014 13:16:38 GMT

Too many synthetic keys are probably not being allowed. How do you want the synthetic keys to be dealt with? Usually, you can rename them to avoid sythetic keys using preceding load, like:

.....

....

Load

PK_AGENCY as Q_Agency,

MONTHY as Q_Month,

PRODUCT as Q_product,

TRANSACTION,

QUALITYSELL,

AMOUNT ;

SQL SELECT

PK_AGENCY,

MONTHY,

PRODUCT,

TRANSACTION,

QUALITYSELL,

AMOUNT

FROM "DBM_MKT".dbo."QUALITY_SELL";

SQL SELECT

PK_AGENCY,

MONTHY,

PRODUCT,

SUMVALUE

FROM "DBM_MKT".dbo."SELL_VALUE";

Re: Synthetic Keys not created automatically

Tue, 03 Jun 2014 13:20:40 GMT

Same error message.

Re: Synthetic Keys not created automatically

jonathandienst — Tue, 03 Jun 2014 13:25:52 GMT

How large are these tables and how much RAM do you have on the machine doing the reload? Have you checked the memory consumption? There should be no synthetic keys being created if you followed my post exactly.

What else are you doing in your script. If you single step, where does the error occur. I suggest that you post your complete script here.

HTH

Jonathan

Re: Synthetic Keys not created automatically

Tue, 03 Jun 2014 13:26:44 GMT

Same error message.

Re: Synthetic Keys not created automatically

Tue, 03 Jun 2014 13:30:41 GMT

Hi Jonathan,

Tables:

Geo: 4385 records

Time: 10 records

QUALITY_SELL: 489777 records

SELL_VALUE: 289075 records

This is my complete script.

Re: Synthetic Keys not created automatically

jonathandienst — Tue, 03 Jun 2014 13:34:27 GMT

Well that's not a lot. What happens if you run these queries in MSQL MS or Toad?

Re: Synthetic Keys not created automatically

Tue, 03 Jun 2014 13:44:39 GMT

Takes seconds to run without any error..