Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

How to join with master calender

Hi,

I have 3 tables data which contains below columns and please help on writing code for data model and creating master calender and joining with date.

Table1 contains region, country, state, transactiondate,sales

Table2 contains region, country, state,group,actualdate,value

Table3 contains region, country, state,id,date,amount

Could you please help me on code for above table joins with calender date, but dates are different in each tables and columns also different.

Thanks

Kanna

1 Solution

Accepted Solutions
Highlighted
Master III
Master III

Re: How to join with master calender

//Code for tables

Table1:

Load

region,

country,

state,

transactiondate as Date,

sales,

'Sales' as Flag

form Table1;

Concatenate

Table2:

LOAD  

region,

country,

state,

group,

actualdate as Date,

value ,

'Value'  as Flag

from Table2;

Concatenate

Table3:

Load 

region,

country,

state,

id,

date as Date,

amount,

'Amount' as Flag

from Table3;

// Code for Master Cal

DATE:

LOAD

MIN(Date) AS MIN_DATE,

MAX(Date) AS MAX_DATE

Resident Table1;

LET vDataMax= PEEK('MAX_DATE','0','DATE');

LET vDataMin= PEEK('MIN_DATE','0','DATE');

MasaterCalendar:

LOAD

$(vDataMin)+IterNo()-1 AS Date

// Date($(vDataMin)+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE $(vDataMin)+IterNo()-1 <= $(vDataMax);

Calendar:

Load *, Quarter &' '&  (right(FiscalYear,2)) as QuarterYear;

LOAD

Floor(Date) as Posting_Num,

Year(YearName(Date,0,4)) as FiscalYear,

subfield(ApplyMap('Quarter',text(Month(Date)),'') , '|' , 1) as TestQuarter,

  

     YearName(Date,0,4) AS FinancialYear,

     Date(Floor(Date),'DD/MM/YYYY') as [Posting Date],

     date(Date,'MMM-YYYY') AS MonthYear,

     date(MonthStart(Date),'MMM-YY') AS MonthYear1,

     Month(Date) AS MonthName,

     If(Num(Month(Date))>3,Num(Month(Date))-3,Num(Month(Date))+9) AS MonthNumber,

     MonthStart(Date) AS MonthStart,

  MonthEnd(Date) AS MonthEnd,

   Year(Date) AS Year,

   quarterstart(Date,0,4) AS QuarterStart,

  quarterend(Date,0,4) AS QuarterEnd,

  quartername(Date,0,4) AS QuarterName,

  Date(Yearstart(Date,0,4)) AS YearStart,

  Date(Yearend(Date,0,4)) AS YearEnd,

  Day(Date) &'-'& Date(Date,'MMM') AS DayMon

Resident  MasaterCalendar;

drop Table MasaterCalendar;

View solution in original post

6 Replies
Highlighted
Master III
Master III

Re: How to join with master calender

Try like this ...

Highlighted
Creator
Creator

Re: How to join with master calender

Thank you so much.. I am not able to qvw.. Can u please share the code.. So that I can create new app for test..that helps me alot..


Thanks

Kanna

Highlighted
Master III
Master III

Re: How to join with master calender

//Code for tables

Table1:

Load

region,

country,

state,

transactiondate as Date,

sales,

'Sales' as Flag

form Table1;

Concatenate

Table2:

LOAD  

region,

country,

state,

group,

actualdate as Date,

value ,

'Value'  as Flag

from Table2;

Concatenate

Table3:

Load 

region,

country,

state,

id,

date as Date,

amount,

'Amount' as Flag

from Table3;

// Code for Master Cal

DATE:

LOAD

MIN(Date) AS MIN_DATE,

MAX(Date) AS MAX_DATE

Resident Table1;

LET vDataMax= PEEK('MAX_DATE','0','DATE');

LET vDataMin= PEEK('MIN_DATE','0','DATE');

MasaterCalendar:

LOAD

$(vDataMin)+IterNo()-1 AS Date

// Date($(vDataMin)+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE $(vDataMin)+IterNo()-1 <= $(vDataMax);

Calendar:

Load *, Quarter &' '&  (right(FiscalYear,2)) as QuarterYear;

LOAD

Floor(Date) as Posting_Num,

Year(YearName(Date,0,4)) as FiscalYear,

subfield(ApplyMap('Quarter',text(Month(Date)),'') , '|' , 1) as TestQuarter,

  

     YearName(Date,0,4) AS FinancialYear,

     Date(Floor(Date),'DD/MM/YYYY') as [Posting Date],

     date(Date,'MMM-YYYY') AS MonthYear,

     date(MonthStart(Date),'MMM-YY') AS MonthYear1,

     Month(Date) AS MonthName,

     If(Num(Month(Date))>3,Num(Month(Date))-3,Num(Month(Date))+9) AS MonthNumber,

     MonthStart(Date) AS MonthStart,

  MonthEnd(Date) AS MonthEnd,

   Year(Date) AS Year,

   quarterstart(Date,0,4) AS QuarterStart,

  quarterend(Date,0,4) AS QuarterEnd,

  quartername(Date,0,4) AS QuarterName,

  Date(Yearstart(Date,0,4)) AS YearStart,

  Date(Yearend(Date,0,4)) AS YearEnd,

  Day(Date) &'-'& Date(Date,'MMM') AS DayMon

Resident  MasaterCalendar;

drop Table MasaterCalendar;

View solution in original post

Highlighted
MVP
MVP

Re: How to join with master calender

Probably best to concatenate into a single fact and alias the date fields to the same field name to link to the master calendar:

Fact:

LOAD region,

  country,

  state,

  transactiondate as date,

  sales,

  'Table1' as source

FROM Table1;

Concatenate(Fact)

LOAD region,

  country,

  state,

  actualdate as date,

  group,

  value,

  'Table2' as source

FROM Table2;

Concatenate(Fact)

LOAD region,

  country,

  state,

  id,

  date,

  amount,

  'Table3' as source

FROM Table3;

Adjust the code to your environment and requirements. Set the source field to more meaningful strings.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator
Creator

Re: How to join with master calender

That's very helpful for me.. Just have one query.

From each table date field is generating may not be all same dates.

That time we are calculataing min and Max value.. Of date.. How can we say it is taking from all tables min and Max values.. I thought  it is taking min and Max values from table1 only... What about remaining tables.

Thanks

Kanna

Highlighted
Master III
Master III

Re: How to join with master calender

I am concatenating all three table with name Table1 means fact table it is taking min and max date from fact table

see like this

Fact:

Load

region,

country,

state,

transactiondate as Date,

sales,

'Sales' as Flag

form Table1;

Concatenate

LOAD  

region,

country,

state,

group,

actualdate as Date,

value ,

'Value'  as Flag

from Table2;

Concatenate

Load 

region,

country,

state,

id,

date as Date,

amount,

'Amount' as Flag

from Table3;