Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Joining Master Calender to two or more tables on Date field

Hi Everyone,

Below is my requirement

TableOrder NoItem No
Order Date
GRN Date
Purchase Order (PO)PPP
Purchase Reciept (PR)PPPP
Master Calender

PP

I have to connect Master Calendar on Date field to Purchase Order Order Date field & Purchase Receipt GRN Date Field.

Order Date id Present in PO & PR.

So that,when user select Any perticular Month-Year,he will get all the Orders generated in that selected month as well as Orders whose GRN generated in that month.

How can I do this?

Thanks

2 Replies
kamalqlik
Valued Contributor

Re: Joining Master Calender to two or more tables on Date field

Hi,

Kiran may be this can help you

Calendar:
LET vDateMin = Num(MakeDate(2010,1,1)); 
LET vDateMax = Num(MakeDate(2011,12,31));  
LET vDateToday = Num(Today()); 

TempCalendar: 
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber, 
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

Calendar: 
LOAD
Date(TempDate) AS CalendarDate, 
Month(TempDate) AS CalendarMonthName, 
Year(TempDate) AS CalendarYear
RESIDENT TempCalendar ORDER BY TempDate ASC;

Link:
LOAD

Order,
OrderNo,etc
Order DATE as CalendarDate,
'OrderDate' as DateTypeFlag
RESIDENT Purcahse Order;
concatenate (Link)
LOAD

GRC No,

etc

Purchase Date as CalendarDate,
'PurcaseDate' as DateTypePurcase
RESIDENT [Purcse Reciept];

DROP TABLE TempCalendar;

Hope it helps you.

Regards

Kamal


Employee
Employee

Re: Joining Master Calender to two or more tables on Date field

A link table or a bridge table can connect one single master calendar to several dates. But why do you want to do that? If you do that you cannot make selections like "Find transactions that have order dates in Oct and GRN dates in November". It's much better to load several calendar tables.

See more on the blog about multiple calendar tables.

HIC

Community Browser