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

[Anonymous]

Hi,

Please can you clarify why are you using two composite keys.

May be try below code:

A:

LOAD [Policy Number],

     CNTYPE,

     Name,

     [Channel code],

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

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

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

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

     Month

FROM

(ooxml, embedded labels, table is Sheet1);

B:

CrossTable(Month,Data,4)

LOAD [Product Name] as CNTYPE,

     [Product bifurcation],

     [Product Type],

     Channel,

     April,

     May,

     June,

     July

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

A1:

LOAD [Policy Number],

     CNTYPE,

     Name,

     [Channel code],

     Channel,

     Month

Resident A;

Left Join(A1)

LOAD [Product Name] as CNTYPE,

     [Product bifurcation],

     [Product Type],

     Channel,

     Month,

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

Resident B;

DROP Table A;

DROP Table B;

NoConcatenate

Final:

load *,

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

Resident A1;

DROP Table A1;

Regards

Neetha

[pra_kale]

swuehl thanks for your reply..

I have tried the way you did and able to create the Key. But the way you rightly said I actually want the Target Vs Actuals for correct dimensions. But my actual problem is Target is in one excel file and Actual values are not available in that excel file but, which  i am generating from different different qvds. For e.g.

1) Channel.qvd....I am getting channels...policy number is a common thing.

2) Policynumbers, Month, Product code....which is my base file to derived actuals month wise.

3) From above file i am using Product code to derive other product related information...which is above excel file.

from above combinations I am generating a key which I thought i can use to map targets..but i think my approach is defiantly wrong.

so, i can you please suggest what should be my approach...so  i will get actual vs target.

Thanks in advance.

[swuehl]

I believe somehow there should be a key to link targets with actuals, and if you haven't succeeded, it could also be that your key field values are not matching (e.g. different format).

It's quite hard to help you with the information provided.

Can't you store some of your tables into QVDs or create some mock up data and add to your Excel file?

[pra_kale]

Hi,

I am attaching below Two sample files...

1)* Actuals*..in that file i have given the pivot to have some idea about

the out-put how it should look..Highlighted product related information

Product bifurcation, Product Type is coming from Target file based on

Product codes which is Associated field.

2)Target file from where I am getting targets.... product wise and

channel wise.

Below given are the channel descriptions based on their codes...BC--Broka,

FC--- Agency, BA-- Banka,CA---Agency, JA---Direct ...this part is in my script as

I not have any excel files for this.

I want Products, Channels as well as Months is in a selections so as per my

selection I can get the out-put.

Thanks in advance.

[swuehl]

Attached two approaches, one using a concatenated fact table and one a key to link two tables.

These should give you a basic idea.

[pra_kale]

Tanks Swuehl You are Great...

It really helps me..just one more help is required...

I have created a  list box to select month, but my problem is the months are get's sorted as per alphabetically means Apr-15, Feb-16, Jun-15 and so on...but I want order like Apr-15, May-15, Jun-15,Jul-15..and so on what changes i required to do in data model..please suggest.

Thanks in advance.

[swuehl]

You need to use QV date and time functions to intepret your input data as dates (so QV will create a dual value, and the values automatically sort by numeric value correctly) and format your dates as you want.

Follow these blog post and the attached tech note:

Get the Dates Right

[qlikviewwizard]

Hi pra_kale

Please close the thread by selecting Correct Answer. Thanks.