topic Re: Link 2 Date Fields in 1 Table to 1 Master Calendar in QlikView

Link 2 Date Fields in 1 Table to 1 Master Calendar

twanqlik — Sat, 10 Feb 2018 17:17:43 GMT

I have a table with 3 fields:

Example_Table:

Fact

Closed_Date

Respons_Date

When I filter on Closed_Date I want to see the sum of Fact.

And of course the same for the Response_Date.

The filtering on the date fields shouldn't of course interfere each other.

This works perfect with expressions like:

=Sum( {<Closed_Date>} Fact)

=Sum( {<Respons_Date>} Fact)

However, eventually I want to have 1 master calendar linked to both the Date Fields. The user should be able to filter on a specific date and that date should be selected in both the Date fields.

I think that for that I need to create a link table between my Example Table and the master calendar, however I'm struggling with that. Any tips?

*Ps, this is an example, when i know how to create this link table i will use it in a bigger dashboard

Re: Link 2 Date Fields in 1 Table to 1 Master Calendar

Anil_Babu_Samineni — Sat, 10 Feb 2018 18:27:04 GMT

PFA

***** Script *****

Sample:

LOAD Fact,

Closed_Date

FROM

[2_Date_Fields.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Sample1:

LOAD Fact,

Respons_Date

FROM

[2_Date_Fields.xlsx]

(ooxml, embedded labels, table is Sheet1);

CalendarMaster:

LOAD

Date(Closed_Date) AS Closed_Date;

Load Date(MinDate + IterNo() -1 ) AS Closed_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(Closed_Date) AS MinDate,

Max(Closed_Date) AS MaxDate

RESIDENT Sample;

LOAD

Date(Respons_Date) AS Respons_Date;

Load Date(MinDate + IterNo() -1 ) AS Respons_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(Respons_Date) AS MinDate,

Max(Respons_Date) AS MaxDate

RESIDENT Sample1;

Re: Link 2 Date Fields in 1 Table to 1 Master Calendar

twanqlik — Sun, 11 Feb 2018 17:56:56 GMT

Thanks, but i don't want to split the 'example table' into 2 tables.

My table in my dashboard has many more fields than only 'Fact'

Re: Link 2 Date Fields in 1 Table to 1 Master Calendar

MK_QSL — Sun, 11 Feb 2018 18:43:27 GMT

Use something like below.

FactTable:
LOAD Fact, 
     Closed_Date, 
     Respons_Date
FROM
[https://community.qlik.com/servlet/JiveServlet/download/1433301-314246/2_Date_Fields.xlsx]
(ooxml, embedded labels, table is Sheet1);


LinkTable:
Load Distinct Fact, Closed_Date as LinkDate, 'Closed' as Flag Resident FactTable;
Load Distinct Fact, Respons_Date as LinkDate, 'Respons' as Flag Resident FactTable;


TempMinMaxDate:
Load 
 Min(LinkDate) as MinDate,
 Max(LinkDate) as MaxDate
Resident LinkTable;


Let vMinDate = Num(Peek('MinDate',0,'TempMinMaxDate'));
Let vMaxDate = Num(Peek('MaxDate',0,'TempMinMaxDate'));


Drop Table TempMinMaxDate;


Calendar:
Load 
 Date(TempDate) as LinkDate,
 Week(TempDate) as Week,
 Month(TempDate) as Month,
 'Q' & Ceil(Month(TempDate)/3) as Quarter,
 Year(TempDate) as Year
;
Load 
 $(vMinDate) + IterNo() - 1 as TempDate 
AutoGenerate 1 
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Use flag in your expressions to distinguish between Closed and Response date.

Re: Link 2 Date Fields in 1 Table to 1 Master Calendar

ss2q — Fri, 01 Mar 2024 18:41:28 GMT

How about if I have two tables and each table has one date which is different from the other? For example, the Order table has the Ordered date and the Invoice table has GL_date and I want to only take the year from the date and use it. For that how will the code be? I will be grateful if you can help

Re: Link 2 Date Fields in 1 Table to 1 Master Calendar

ss2q — Fri, 01 Mar 2024 21:07:13 GMT

How about if I have two tables order lines and invoice? I have ordered the date in order line table and gl_date in the invoice table. I have already extracted from this each date in each table because I want to make an analysis using Year. So, how to relate the year of order date and year of invoice date