topic Re: Composite Key Issue in QlikView

Composite Key Issue

pra_kale — Fri, 07 Aug 2015 16:42:56 GMT

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,

A&B&C as Key

FROM

(ooxml, embedded labels, table is Sheet1);

....

---

----

load *,

name&dob&place as Key

----

-----

Thanks in advance

Re: Composite Key Issue

swuehl — Fri, 07 Aug 2015 16:57:52 GMT

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

Re: Composite Key Issue

sunny_talwar — Fri, 07 Aug 2015 16:58:01 GMT

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

Re: Composite Key Issue

pra_kale — Sat, 08 Aug 2015 14:17:17 GMT

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

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

Re: Composite Key Issue

pra_kale — Sat, 08 Aug 2015 14:17:42 GMT

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

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

Re: Composite Key Issue

swuehl — Sat, 08 Aug 2015 14:25:20 GMT

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.

Re: Composite Key Issue

sunny_talwar — Sat, 08 Aug 2015 14:27:23 GMT

Two alternatives:

Alternative 1:

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:

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

Re: Composite Key Issue

maximiliano_vel — Sat, 08 Aug 2015 14:27:53 GMT

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;

Re: Composite Key Issue

pra_kale — Sun, 09 Aug 2015 05:12:51 GMT

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.

Re: Composite Key Issue

swuehl — Sun, 09 Aug 2015 08:40:58 GMT

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?

Re: Composite Key Issue

Anonymous — Sun, 09 Aug 2015 09:56:27 GMT

Hi,

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

May be try below code:

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

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

Re: Composite Key Issue

pra_kale — Mon, 10 Aug 2015 17:17:19 GMT

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.

Re: Composite Key Issue

swuehl — Mon, 10 Aug 2015 17:28:07 GMT

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?

Re: Composite Key Issue

pra_kale — Tue, 11 Aug 2015 02:23:57 GMT

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.

Re: Composite Key Issue

swuehl — Tue, 11 Aug 2015 19:37:10 GMT

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

These should give you a basic idea.

Re: Composite Key Issue

pra_kale — Thu, 13 Aug 2015 17:08:14 GMT

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.

Re: Composite Key Issue

swuehl — Thu, 13 Aug 2015 17:12:15 GMT

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

Re: Composite Key Issue

qlikviewwizard — Fri, 14 Aug 2015 03:21:54 GMT

Hi pra_kale

Please close the thread by selecting Correct Answer. Thanks.