Cannot Link Master Calendar

[l47474747]

Dear Masters,

I am having trouble implementing my 13 period calendar into Qliksense. Could you help?

This is my master calendar:

YEAR	PERIOD	STARTDATE	ENDDATE	PAYPERIOD	PAYDATE	PAYSTART	PAYEND	No Days	Report Against
2006	1	08/01/2006	04/02/2006	44	10/02/2006	08/01/2006	04/02/2006	28	C
2006	2	05/02/2006	04/03/2006	48	10/03/2006	05/02/2006	04/03/2006	28	C
2006	3	05/03/2006	31/03/2006	52	07/04/2006	05/03/2006	01/04/2006	27	C
2006	4	01/04/2006	29/04/2006	4	05/05/2006	02/04/2006	29/04/2006	29	F1
2006	5	30/04/2006	27/05/2006	8	02/06/2006	30/04/2006	27/05/2006	28	F1
2006	6	28/05/2006	24/06/2006	12	30/06/2006	28/05/2006	24/06/2006	28	F1
2006	7	25/06/2006	22/07/2006	16	28/07/2006	25/06/2006	22/07/2006	28	F2
2006	8	23/07/2006	19/08/2006	20	25/08/2006	23/07/2006	19/08/2006	28	F2
2006	9	20/08/2006	16/09/2006	24	22/09/2006	20/08/2006	16/09/2006	28	F2
2006	10	17/09/2006	14/10/2006	28	20/10/2006	17/09/2006	14/10/2006	28	F3
2006	11	15/10/2006	11/11/2006	32	17/11/2006	15/10/2006	11/11/2006	28	F3
2006	12	12/11/2006	09/12/2006	36	15/12/2006	12/11/2006	09/12/2006	28	F3
2006	13	10/12/2006	06/01/2007	40	12/01/2007	10/12/2006	06/01/2007	28	F3

And I want to link it to this data:

Form id	form submission id	date submitted	Are you reporting a Surplus or Loss, please select below	Amount of the anomaly incurred?
200	116532	20/06/2019 18:28	Loss	7.75
200	116407	19/06/2019 10:27	Loss	2.88
200	116250	17/06/2019 23:46	Surplus	4.98
200	116248	17/06/2019 23:24	Loss	7.98
200	116224	17/06/2019 18:19	Surplus	4.65

This is just a snippet of each table.

Thanks,

[marcus_sommer · Accepted solution]

It's not really a master-calendar but it could be used to create one, for example with:

cal: load *, date(STARTDATE + iterno() - 1) as Date from A while STARTDATE + iterno() - 1 <= ENDDATE;

and with:

date(floor([date submitted])) as Date

you creates a real date within your fact-table and both tables would be linked over this new field Date.

- Marcus

[l47474747]

Hi Marcus,

Thanks for getting in touch. where should I input these lines of code?

Sorry about the inexperience, I am new.

[marcus_sommer]

It's just:

NewMasterCalendar: load *, date(STARTDATE + iterno() - 1) as Date
from YourMasterCalendar while STARTDATE + iterno() - 1 <= ENDDATE;

Submission: load *, date(floor([date submitted])) as Date
from YourSubmissionTable;

- Marcus