topic Re: Joining Cross Table with data and calendar in QlikView

Joining Cross Table with data and calendar

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

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

Re: Joining Cross Table with data and calendar

jwjackso — Sat, 17 Feb 2018 20:37:46 GMT

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;

Re: Joining Cross Table with data and calendar

khaycock — Sat, 17 Feb 2018 21:59:15 GMT

How do I connect the category field though?

This doesn't match them

Re: Joining Cross Table with data and calendar

jwjackso — Sat, 17 Feb 2018 22:10:59 GMT

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

Re: Joining Cross Table with data and calendar

khaycock — Sat, 17 Feb 2018 22:23:17 GMT

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

Re: Joining Cross Table with data and calendar

jwjackso — Sat, 17 Feb 2018 22:27:06 GMT

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;

Re: Joining Cross Table with data and calendar

khaycock — Sat, 17 Feb 2018 22:36:21 GMT

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

Re: Joining Cross Table with data and calendar

jwjackso — Sat, 17 Feb 2018 23:54:57 GMT

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

Re: Joining Cross Table with data and calendar

khaycock — Mon, 19 Feb 2018 11:11:44 GMT

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

Re: Joining Cross Table with data and calendar

jwjackso — Mon, 19 Feb 2018 14:03:34 GMT

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)

Re: Joining Cross Table with data and calendar

khaycock — Wed, 21 Feb 2018 14:46:25 GMT

Yey that worked! Thank you

Re: Joining Cross Table with data and calendar

khaycock — Thu, 22 Feb 2018 16:53:46 GMT

Actually Jerry,

Instead of using:

AEA_DATA:

FiscalMonthYear as AEA_FiscalMonthYear,

Key,

Category as AEA_EUROPE,

AEAData

I used this:

AEA_DATA:

Load

Key,

Category AS AEA_EUROPE,

Month&'-'&Year AS AEA_FiscalMonthYear,

AEAData

Because of my Month and Year fields...

And it worked initially when only a specific month was selected. But when I want to view all of the months together as a years view, the data returns the same amount of each month, rather than calculating different amount like its meant to.

Here is the results I am getting for the year view of Jan and Feb:

When actually it should be showing these results:

I think its a subtotal of the whole year but displaying it on every month? Not sure why some have 0 though. Any ideas?