topic Removing the Synthetic Key which are formed my StartDate and EndDate in QlikView

Removing the Synthetic Key which are formed my StartDate and EndDate

Fri, 29 Apr 2011 09:26:21 GMT

Can some one please tell me how to remove the Synthetic Keys which is there in the below model becuase of StartDate and EndDate. I tried in few ways but it didn't happen. Can some one please guide me on this please!

Removing the Synthetic Key which are formed my StartDate and EndDate

daveamz — Fri, 29 Apr 2011 09:45:21 GMT

Hi Attitude,

Try this:

Data:

load

StartDate&EndDate as Key1,

StarDate,

EndDate,

from ... ;

qualify StartDate, EndDate;

Data-1:

load

StartDate&EndDate as Key1,

StartDate,

EndDate,

date

from ....;

The Synthetic Key is created because you have 2 equal fields in both tables (Data and Data-1)

Best regards!

Removing the Synthetic Key which are formed my StartDate and EndDate

Fri, 29 Apr 2011 11:15:32 GMT

Hi David

Before getting your suggestion I did the samething only. What I have noticed is now there is no table called as Data-1. Could you please tell me what have done is correct or not. I have given you the script below which I am working on. Please look into it and give some solution.


Data:
LOAD A1,
 A2,
 A3,
 A4,
 A5,
 A6,
 A7,
 A8,
 A9,
 A10,
 A11,
 A12,
 A13,
 A14,
 //TimeStamp(A15) as StartDate,
 //TimeStamp(A16) as EndDate,
 date(floor(A15)) as StartDate,
 date(floor(A16)) as EndDate,
 StartDate&EndDate as Key1,
 A17 as StartDate1,
 A18 as EndDate1,
 A19,
 A20
FROM

(ooxml, embedded labels, table is Sheet1);
MinMax:
LOAD min(StartDate) as MinDate,
 max(EndDate) as MaxDate
RESIDENT Data
GROUP by 1;
LET vMinDate= peek('MinDate');
LET vMaxDate= peek('MaxDate');
LET vNoDays = vMaxDate - vMinDate + 1;
DROP Table MinMax;
Calendar:
LOAD *,
 year(date) as Year,
 month(date) as Month,
 week(date) as Week,
 1 as DVal;
LOAD DISTINCT
 date('$(vMaxDate)' - recno() + 1) as date
AUTOGENERATE $(vNoDays);
IntervalMatch(date)
LOAD StartDate, EndDate,StartDate&EndDate as Key1 RESIDENT Data;

Thanks in advance

Removing the Synthetic Key which are formed my StartDate and EndDate

sushil353 — Fri, 29 Apr 2011 11:21:03 GMT

hi Attitude,

try autogenerate with start date and end date...

HTH

Removing the Synthetic Key which are formed my StartDate and EndDate

Mon, 02 May 2011 06:54:22 GMT

Hi Sushil

I have never done that before! Could you please do the required changes in the above script please.

Removing the Synthetic Key which are formed my StartDate and EndDate

amars — Mon, 02 May 2011 07:22:46 GMT

Hi,

You can make use of Autonumber(StartDate&EndDate) function to avoid the synthetic key. More ever if u have a sample of ur application we can help.

Thanks

Removing the Synthetic Key which are formed my StartDate and EndDate

Mon, 02 May 2011 07:51:17 GMT

Hi Amars

The application which is there below is the same application which I am working on. Please kindly help me out!

http://community.qlik.com/forums/t/44172.aspx

Removing the Synthetic Key which are formed my StartDate and EndDate

daveamz — Mon, 02 May 2011 08:09:54 GMT

Hi Amaras,

Please check the modified script.

Regards

Re: Removing the Synthetic Key which are formed my StartDate and EndDate

amars — Tue, 10 May 2011 10:52:51 GMT

Hi,

Herewith attached is the file. Sorry for replying late but the community was blocked when I was trying to reply.

Thanks

Removing the Synthetic Key which are formed my StartDate and EndDate

Anonymous — Thu, 23 Jun 2011 06:13:20 GMT

Best way to remove the Synthetic Key to qualify the table and unqulify the particular field wich need to be linked. Please correct me if I am unable to understand your query.

Thanks & regards

skumar