topic Re: Synthetic key due to preceding load in QlikView

Synthetic key due to preceding load

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I am getting synthetic key when creating calculated field in preceding load. The bottom load is the source for preceding load, then why the syn key.

I renamed the field [Debit/Credit] to Spending in the preceding load, but not sure why QV is giving synthetic key.

If i create the Spending field without preceding load, no syn key is generated.

Facts:

LOAD

Date,

[Debit/Credit],

if([Debit/Credit]<0,[Debit/Credit],Null()) as Spending

;

LOAD Date,

[Debit/Credit]

FROM

$(vPath)\File.xlsx

(ooxml, embedded labels, table is Sheet1)

;

$Syn 1 = Date+Debit/Credit

Please clarify.

Thanks!!

Re: Synthetic key due to preceding load

makrishnaraj — Thu, 14 Jul 2016 07:36:49 GMT

Use this below script

Facts:

LOAD

if([Debit/Credit]<0,[Debit/Credit],Null()) as Spending

;

LOAD Date,

[Debit/Credit]

FROM

$(vPath)\File.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Synthetic key due to preceding load

qlikview979 — Thu, 14 Jul 2016 07:43:05 GMT

Facts:

LOAD

Date,

if([Debit/Credit]<0,[Debit/Credit],Null()) as Spending

;

LOAD Date,

[Debit/Credit]

FROM

$(vPath)\File.xlsx

(ooxml, embedded labels, table is Sheet1)

;

Re: Synthetic key due to preceding load

qlikview979 — Thu, 14 Jul 2016 07:46:59 GMT

Hi ,

In your Script Please Comment [Debit/Credit] or give rename any one common field in your tables

Facts:

LOAD

Date,

// [Debit/Credit] (Commented)

[Debit/Credit] as [Debit/Credit1] (Renamed)

if([Debit/Credit]<0,[Debit/Credit],Null()) as Spending

;

LOAD

Date,

[Debit/Credit]

FROM

$(vPath)\File.xlsx

(ooxml, embedded labels, table is Sheet1)

;

Re: Synthetic key due to preceding load

makrishnaraj — Thu, 14 Jul 2016 07:50:34 GMT

It seems to be you are already loading the table with Date & [Debit/Credit] before or after this load.

Hope this helps

Re: Synthetic key due to preceding load

maniram23 — Thu, 14 Jul 2016 08:04:10 GMT

Hi,

Please Check it.

Facts:

LOAD *,

if([Debit/Credit]<0,[Debit/Credit],Null()) as Spending;

LOAD Date,

[Debit/Credit]

FROM

$(vPath)\File.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Synthetic key due to preceding load

surajap123 — Thu, 14 Jul 2016 08:22:55 GMT

Thanks for all the replies.

I know how to fix the issue. I am wondering why synthetic key is generating.

As per my knowledge, i assume that the bottom LOAD statement is just like 'FROM table..' statement. for the upper LOAD statement. So fields in the bottom statement should not dropped when we write preceeding load.

correct me if i am wrong.

Re: Synthetic key due to preceding load

Peter_Cammaert — Thu, 14 Jul 2016 08:39:01 GMT

You are right, a preceding LOAD cannot be responsible all by itself for a Synthetic key. Because the preceding LOAD will result in only one table. makrishnaraj is right, you create another table with the exact same two fields either before or after this LOAD.

After a reload that creates a synthetic key, open Table Viewer (Ctrl-T) to find out which tables are connecting using more than one key field.

Best,

Peter