Synthetic key due to preceding load

[surajap123]

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!!

[makrishnaraj]

Hi

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);

[qlikview979]

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)

;

[qlikview979]

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)

;

[makrishnaraj]

Hi

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

Hope this helps

[maniram23]

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);

[surajap123]

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.

[Peter_Cammaert]

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