Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
How to remove synthetic Key in Interval Match?
You may JOIN the tables, see the attached example
HTH
Peter
hi,3611,
the eample given by peeter is working for me
thanks peeter
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.
Why?
12 is just the sum of the individual values? What is the sense of this calculation?
Peter
Thank you peter it works for me...
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).
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!
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;
Thank you!