Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Joining Cross Table with data and calendar

I have a cross table of data like below:

CrossTable(FiscalMonth, Data, 3)

LOAD Key,

     Category,

     Year,

     Jan,

     Feb,

     Mar,

     Apr,

     May,

     Jun,

     Jul,

     Aug,

     Sep,

     Oct,

     Nov,

     Dec

That I need to link to both the data (Category AS AEA_EUROPE) and also need to link the month and year to the calendar (either FiscalMonth and FiscalYear or FiscalMonthYear) but when I connect the both, I get loops. What is the best way to get them all linked without getting these loops?

Thanks

Tags (2)
11 Replies
Highlighted
Specialist
Specialist

Re: Joining Cross Table with data and calendar

Depending on how your FiscalMonthYear is formatted,

Data:

CrossTable(FiscalMonth, Data, 3)

LOAD Key,

     Category,

     Year,

     Jan,

     Feb,

     Mar,

     Apr,

     May,

     Jun,

     Jul,

     Aug,

     Sep,

     Oct,

     Nov,

     Dec;

Data2:

load Key,

        Category,

        FiscalMonth&'-'&Year as FiscalMonthYear,

        Data

Resident Data;

Drop Table Data;

Highlighted
Creator
Creator

Re: Joining Cross Table with data and calendar

How do I connect the category field though?

This doesn't match them

Highlighted
Specialist
Specialist

Re: Joining Cross Table with data and calendar

Is AEA_EUROPE a calendar field?  Can you show the fields of the calendar?

Creator
Creator

Re: Joining Cross Table with data and calendar

No AEA_EUROPE is just the name of the category field in another table. It has the same values as the Category field in the cross table.

The calendar has the following fields:

ENTERED_PERIOD_NAME

PERIOD_NAME

PERIOD_YEAR

PERIOD_NUM

QUARTER_NUM

PERIOD_DATE

QUARTER_END_DATE

QUARTER_START_DATE

YEAR_END_DATE

YEAR_START_DATE

DATE

Year

Month

MonthYear

MonthNumYear

Quarter

QuarterYear

Week

WeekNumber

Weekday

Day

DateMMDD

FiscalDay

MONTH_START_DATE

MONTH_START

MONTH_END_DATE

MONTH_END

QUARTER_START

QUARTER_END

YEAR_START

YEAR_END

FiscalMonth

FiscalMonthYear

FiscalQuarter

FiscalQuarterYear

and then a bunch of flags.

I have the format of FiscalMonth as MMM and Year as YY so it matches with FiscalMonthYear which is MMM-YY but I'm not having much luck of putting the data together

Highlighted
Specialist
Specialist

Re: Joining Cross Table with data and calendar

As long as the other table isn't connected to the calendar, can't you rename Category as below?

Data2:

load Key,

        Category as AEA_EUROPE,

        FiscalMonth&'-'&Year as FiscalMonthYear,

        Data

Resident Data;

Highlighted
Creator
Creator

Re: Joining Cross Table with data and calendar

Problem is that there is another orders table that is linked to the calendar as SHIPMENT_DATE AS DATE

Highlighted
Specialist
Specialist

Re: Joining Cross Table with data and calendar

Are you getting a circular reference between the new data, the orders table and the calendar.  If so, which fields are common?

Highlighted
Creator
Creator

Re: Joining Cross Table with data and calendar

Here is a screenshot of the tables with the link I also need..

Market.PNG

Highlighted
Specialist
Specialist

Re: Joining Cross Table with data and calendar

Depending on how you are going to use the AEA_DATA, you could link the AEA_DATA to the ITEM table and then use the P() function in your calculations for the AEA_DATA.

AEA_DATA would look like this:

FiscalMonthYear as AEA_FiscalMonthYear,

Key,

Category as AEA_EUROPE,

AEAData

In calculations for the AEA_DATA,

Sum({$<AEA_FiscalMonthYear=P(FiscalMonthYear)>}AEAData)