topic How to remove synthetic Key in Interval Match? in QlikView

How to remove synthetic Key in Interval Match?

Fri, 19 Nov 2010 10:04:11 GMT

How to remove synthetic Key in Interval Match?

prieper — Fri, 19 Nov 2010 10:11:17 GMT

You may JOIN the tables, see the attached example

HTH
Peter

How to remove synthetic Key in Interval Match?

SunilChauhan — Fri, 19 Nov 2010 10:25:33 GMT

hi,3611,

the eample given by peeter is working for me

thanks peeter

Re: How to remove synthetic Key in Interval Match?

sateeshkumar — Mon, 15 Dec 2014 12:34:08 GMT

if I calculate sum(val) then it is giving value as 51, but it should be 12. how to solve this problem.

Thanks in advance.

Re: How to remove synthetic Key in Interval Match?

prieper — Mon, 15 Dec 2014 21:39:47 GMT

Why?

12 is just the sum of the individual values? What is the sense of this calculation?

Peter

Re: How to remove synthetic Key in Interval Match?

Tue, 16 Dec 2014 05:49:33 GMT

Thank you peter it works for me...

Re: How to remove synthetic Key in Interval Match?

sateeshkumar — Tue, 16 Dec 2014 06:00:20 GMT

Hi peter,

Thanks for reply, Actually my dashboard having the kpi like total calls(same as value field) in the period (start_date to end_date). that needs sum(calls) expression. but the value is showing wrong details (like sum(val) in above file).

Re: How to remove synthetic Key in Interval Match?

joey_lutes — Mon, 23 May 2016 20:57:03 GMT

I'm sure the answer is awesome, but those of us with Qlik Sense are out of luck with a qvw.

Can you paste code by chance?

Thank you!

Re: How to remove synthetic Key in Interval Match?

trdandamudi — Mon, 23 May 2016 21:05:43 GMT

As requested below is the code:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='€#,##0.00;-€#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

// ==== Load Activities ==================================================

Data:

LOAD * INLINE [Date, Key1, Other

2010-01-01, A, z

2010-02-01, A, a

2010-03-01, A, z

2010-04-01, A, b

2010-05-01, A, d

2010-06-01, A, e

2010-01-01, B, f

2010-02-01, B, t

2010-03-01, B, k

2010-04-01, B, e

2010-05-01, B, z

2010-06-01, B, a];

// ===== Load the Contract ================================================

Contract:

LOAD * INLINE [Cntrct_From, Cntrct_To, Key1, Val

2010-01-01, 2010-12-31, A, 5

2010-01-01, 2010-03-30, B, 3

2010-04-01, 2010-12-31, B, 4];

// ==== Link Fields from Data with Contract into the Contract-Table ========

INNER JOIN INTERVALMATCH (Date, Key1) LOAD Cntrct_From, Cntrct_To, Key1 RESIDENT Contract;

// ==== Join into one Table only, remove contract-Data =====================

JOIN (Data) LOAD * RESIDENT Contract;

DROP TABLE Contract;

DROP FIELD Cntrct_From, Cntrct_To;

Re: How to remove synthetic Key in Interval Match?

joey_lutes — Mon, 23 May 2016 21:10:19 GMT

Thank you!