Composite Key Issue

[pra_kale]

Hi,

I have uploaded one excel sheet with 10 columns and created one composite key by concatenating 5 columns.

I have created another composite key but by concatenating 5 columns from the data not by loading any external file, to create a association between this two.

But, now i am unable to create a association, as my script gives me a error 'can not have Two columns with one name'. If i make the changes in the column names, how I will create a association between this two ?

can any one help to resolve the Issue..

e.g.

LOAD A,

     B,

     C,

     D,

A&B&C as Key

FROM

(ooxml, embedded labels, table is Sheet1);

....

---

----

load *,

name&dob&place as Key

----

-----

Thanks in advance

[swuehl]

You're right, you create a link between tables by having a field with same name in each of the tables.

But here I think the issue is different, the error message is telling you that you have two fields with same name in the same table LOAD statement. probably because 'Key' is also part of your star symbol * field name expansion.

Hard to see from your sample, because ----- is not telling much ...

[sunny_talwar]

A cleaner script will help us understand where exactly the issue is. Difficult to see what might be the issue. Can you post more parts of the script?

Best,

Sunny

[pra_kale]

Thanks for your reply..below given is the script.

Details files contains actuals where as my Product file contains expected sales month on month with the given combination which I have created through composite key.

I have created one more composite key through data because I want to track Actuals Vs expected for that combination.

But my below given script gives me a error "Field name must be unique within table"

can you please help to resolve the issue..

A:

LOAD [Policy Number],

     CNTYPE,

     Name,

     [Channel code],

     Month

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join(A)

LOAD [Product Name] as CNTYPE,

     [Product bifurcation],

     [Product Type],

     Channel,

     April,

     May,

     June,

     July,

     [Product Name]&[Product bifurcation]&[Product Type]&Channel as Key

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

load *,

if(([Channel code])='FC','Agency',

  if(([Channel code])='BC','Broka',

   if(([Channel code])='JA','Agency',

    if(([Channel code])='BA','Banka')))) as final_channel

Resident A;

DROP Table A;

NoConcatenate

Final1:

LOAD*,

CNTYPE&[Product bifurcation]&[Product Type]&final_channel as Key

Resident Final;

DROP Table Final;

EXIT Script  

[pra_kale]

Thanks for your reply..below given is the script.

Details files contains actuals where as my Product file contains expected sales month on month with the given combination which I have created through composite key.

I have created one more composite key through data because I want to track Actuals Vs expected for that combination.

But my below given script gives me a error "Field name must be unique within table"

can you please help to resolve the issue..

A:

LOAD [Policy Number],

     CNTYPE,

     Name,

     [Channel code],

     Month

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join(A)

LOAD [Product Name] as CNTYPE,

     [Product bifurcation],

     [Product Type],

     Channel,

     April,

     May,

     June,

     July,

     [Product Name]&[Product bifurcation]&[Product Type]&Channel as Key

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

load *,

if(([Channel code])='FC','Agency',

  if(([Channel code])='BC','Broka',

   if(([Channel code])='JA','Agency',

    if(([Channel code])='BA','Banka')))) as final_channel

Resident A;

DROP Table A;

NoConcatenate

Final1:

LOAD*,

CNTYPE&[Product bifurcation]&[Product Type]&final_channel as Key

Resident Final;

DROP Table Final;

EXIT Script  

[swuehl]

Your Table A contains a field 'Key' after the JOIN, right?

Then you do a resident LOAD of table A with all fields, and create an additional field 'final_channel' --> Final table.

Then, you do a resident LOAD of all fields (remember the 'Key' field from Table A?) and you try to create an additional field 'Key'. Ooops, there are two fields named 'Key'.

Not sure what you are trying to do. but either removing one of the two fields named the same or renaming one 'Key' field to 'OtherKey' should help.

[sunny_talwar]

Two alternatives:

Alternative 1:

A:

LOAD [Policy Number],

    CNTYPE,

    Name,

    [Channel code],

    Month

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join(A)

LOAD [Product Name] as CNTYPE,

    [Product bifurcation],

    [Product Type],

    Channel,

    April,

    May,

    June,

    July,

    [Product Name]&[Product bifurcation]&[Product Type]&Channel as Key

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

LOAD *,

        if(([Channel code])='FC','Agency',

        if(([Channel code])='BC','Broka',

        if(([Channel code])='JA','Agency',

        if(([Channel code])='BA','Banka')))) as final_channel

Resident A;

DROP Table A;

DROP Field Key;

NoConcatenate

Final1:

LOAD *,

        CNTYPE&[Product bifurcation]&[Product Type]&final_channel as Key

Resident Final;

DROP Table Final;

EXIT Script

Alternative 2:

A:

LOAD [Policy Number],

    CNTYPE,

    Name,

    [Channel code],

    Month

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join(A)

LOAD [Product Name] as CNTYPE,

    [Product bifurcation],

    [Product Type],

    Channel,

    April,

    May,

    June,

    July,

    [Product Name]&[Product bifurcation]&[Product Type]&Channel as Key

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

LOAD *,

        if(([Channel code])='FC','Agency',

        if(([Channel code])='BC','Broka',

        if(([Channel code])='JA','Agency',

        if(([Channel code])='BA','Banka')))) as final_channel

Resident A;

DROP Table A;

NoConcatenate

Final1:

LOAD *,

        CNTYPE&[Product bifurcation]&[Product Type]&final_channel as Key1

Resident Final;

DROP Table Final;

EXIT Script

Since Key is already available in Final table, you are recreating it in Final1. So to fix it either call it Key1 or Drop Key from Final Table.

HTH

Best,

Sunny

[maximiliano_vel]

You are defining Key two times, first:

Left Join(A)

LOAD [Product Name] as CNTYPE,

     [Product bifurcation],

     [Product Type],

     Channel,

     April,

     May,

     June,

     July,

     [Product Name]&[Product bifurcation]&[Product Type]&Channel as Key

FROM

(ooxml, embedded labels, table is Sheet1);

and then when you loading (when you call *)

NoConcatenate

Final1:

LOAD *,

CNTYPE&[Product bifurcation]&[Product Type]&final_channel as Key

Resident Final;

[pra_kale]

Thanks Swuehl for your reply.

Actually i am  little confused now.I am attaching a small excel file in that i am giving the Targets according to the set of combination and against that i have to show the actuals. Means i will select the combination like..

First, Channel.

second, Product Type

  Third, Product bifurcation

      Four, Month

Accordingly I will get the Targets from the excel sheet and then Actuals done from the data.

E.g. final out-put looks like this

  Channel....Agency....In selection

      Product Type....Linked.....In selection

          Product bifurcation....PAR....In selection

           Month.......April, May, June...In selection

         Target   Actuals  Remark

April    100        50          Poor

May    200        250         Good

June   300        150         Poor

Thanks in Advance.

[swuehl]

I'm a little confused now, too.

I think your excel file only shows half of the information needed, since your issue is, as far as I understand, getting Actuals and Targets combined in one table with correct dimension and selection filtering. Your data is missing the actuals part or a definition how this table looks like.

To read in your target values, I would suggest to use CROSSTABLE LOAD prefix to transform the month fields into a  Month / Target Value combination (Crosstable to straight table).

See attached for a sample.

I also added a Key, but since I am missing the relevant information about what you want to do, the Key alone is quite useless.

For example, you don't need a combined Key field if you use a single fact table for actuals and targets by concatenating both tables. If your issue is handling mixed granularity, have a look at

Generic keys

Looking at your first posts, I still think that your original issue is having two fields named the same in one table LOAD, because your star symbol field name expansion

LOAD *, ...

already contained a field named Key, and you are trying to create another field named Key.

Is this understood, before we move on?