Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Removing the Synthetic Key which are formed my StartDate and EndDate

Hi

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!

error loading image

9 Replies
daveamz
Partner - Creator III
Partner - Creator III

Hi Attitude,

Try this:

Data:

load

StartDate&EndDate as Key1,

StarDate,

EndDate,

A1

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!

Not applicable
Author

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

sushil353
Master II
Master II

hi Attitude,

try autogenerate with start date and end date...

HTH

Not applicable
Author

Hi Sushil

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

amars
Specialist
Specialist

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

Not applicable
Author

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

daveamz
Partner - Creator III
Partner - Creator III

Hi Amaras,

Please check the modified script.

Regards

amars
Specialist
Specialist

Hi,

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

Thanks

Anonymous
Not applicable
Author

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