topic Re: How to join with master calender in QlikView

How to join with master calender

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to join with master calender

prma7799 — Thu, 22 Jun 2017 05:40:11 GMT

Try like this ...

Re: How to join with master calender

Anonymous — Thu, 22 Jun 2017 06:35:38 GMT

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

Re: How to join with master calender

prma7799 — Thu, 22 Jun 2017 06:39:21 GMT

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

Re: How to join with master calender

jonathandienst — Thu, 22 Jun 2017 06:45:06 GMT

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.

Re: How to join with master calender

Anonymous — Thu, 22 Jun 2017 06:47:19 GMT

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

Re: How to join with master calender

prma7799 — Thu, 22 Jun 2017 06:50:38 GMT

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;