How to join with master calender

[Anonymous]

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

[prma7799]

Try like this ...

[Anonymous]

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

[prma7799 · Accepted solution]

//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;

[jonathandienst]

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.

[Anonymous]

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

[prma7799]

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;