Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
khaycock
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

11 Replies
jwjackso
Specialist III
Specialist III

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;

khaycock
Creator
Creator
Author

How do I connect the category field though?

This doesn't match them

jwjackso
Specialist III
Specialist III

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

khaycock
Creator
Creator
Author

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

jwjackso
Specialist III
Specialist III

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;

khaycock
Creator
Creator
Author

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

jwjackso
Specialist III
Specialist III

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

khaycock
Creator
Creator
Author

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

Market.PNG

jwjackso
Specialist III
Specialist III

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)