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

How to remove synthetic Key in Interval Match?

Hi

How to remove synthetic Key in Interval Match?

1 Solution

Accepted Solutions
prieper
Master II
Master II

You may JOIN the tables, see the attached example

HTH
Peter

View solution in original post

9 Replies
prieper
Master II
Master II

You may JOIN the tables, see the attached example

HTH
Peter

SunilChauhan
Champion
Champion

hi,3611,

the eample given by peeter is working for me

thanks peeter

Sunil Chauhan
sateeshkumar
Creator
Creator

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.

prieper
Master II
Master II

Why?

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

Peter

Not applicable
Author

Thank you peter it works for me...

sateeshkumar
Creator
Creator

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

joey_lutes
Partner - Creator
Partner - Creator

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!

trdandamudi
Master II
Master II

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;

joey_lutes
Partner - Creator
Partner - Creator

Thank you!