Re: Combining periods of different Income statemen... - Qlik Community

Anonymous · ‎2013-01-18

Hi, I've been struggling with something but am sure there's a simple way to fix it.

I've got 3 different Income Statements (Actual, Budget, Projected) for multiple periods which I want to compare in one pivot table. I've linked the income and expenses from the different statements with an account number (Account) - see below. Becuase I want to compare it on one pivot table I want to be able to select a period only once and the figures must be updated. I've therefore given all periods the same field name but are getting a synthetic key becuase the statements are already linked to each other by Account. Is there a way that I can link the 3 different periods to each other without getting the synthetic key?

//Budget Income Statement
Directory;
CrossTable(IS_Period, Budget_amount, 2)
LOAD Account, 
     [Account name] as [Budget Account name], 
     [2012.1], 
     [2012.11], 
     [2012.12], 
     [2013.01], 
     [2013.02], 
     [2013.03], 
     [2013.04], 
     [2013.05], 
     [2013.06], 
     [2013.07], 
     [2013.08], 
     [2013.09], 
     [2013.1], 
     [2013.11], 
     [2013.12]
FROM
[...]
(ooxml, embedded labels, table is [Budget_Income statement]);
//PROJECTED INCOME STATEMENT
Directory;
CrossTable(IS_Period, Projected_amount, 2)
LOAD Account, 
     [Account name] as [Projected Account name], 
     [2012.1], 
     [2012.11], 
     [2012.12], 
     [2013.01]
FROM
[...]
(ooxml, embedded labels, header is 1 lines, table is Projected_IS);
//ACTUAL TRIAL BALANCE
Directory;
CrossTable(IS_Period, Pastel_amount, 2)
LOAD Account, 
     [Account name] as [Pastel Account name], 
     [2012.1], 
     [2012.11],
     [2012.12]
FROM
[...]
(ooxml, embedded labels, table is [Actual TB]);

nirav_bhimani · ‎2013-01-18

Hi,

You have to use link table for that, Can you share demo file so that I can help you further.

Regards,

Nirav Bhimani

Anonymous · ‎2013-01-18

Hi Nariv

I appreciate your help. See attached for a demo file.

nirav_bhimani · ‎2013-01-18

Hi,

Try this Script,

//Budget Income Statement

Directory;

Tbl1:

CrossTable(IS_Period, Budget_amount)

LOAD Account,

[2012.1],

[2012.11],

[2012.12],

[2013.01],

[2013.02],

[2013.03],

[2013.04],

[2013.05],

[2013.06],

[2013.07],

[2013.08],

[2013.09],

[2013.1],

[2013.11],

[2013.12]

FROM

[Proposed Budget.xlsx]

(ooxml, embedded labels, table is [Budget_Income statement]);

//PROJECTED INCOME STATEMENT

Directory;

Tbl2:

CrossTable(IS_Period, Projected_amount, 1)

LOAD Account,

[2012.1],

[2012.11],

[2012.12],

[2013.01]

FROM

(ooxml, embedded labels, header is 1 lines, table is Projected_IS);

//ACUTAL TRIAL BALANCE

Directory;

Tbl3:

CrossTable(IS_Period, Actual_amount, 1)

LOAD Account,

[2012.1],

[2012.11],

[2012.12]

FROM

(ooxml, embedded labels, table is [Actual TB]);

//New Tables with Composite Key...................................................................

NT1:

Load *, Account &'-'&IS_Period as Tbl1_Link

Resident Tbl1;

NT2:

Load *, Account &'-'&IS_Period as Tbl2_Link

Resident Tbl2;

NT3:

Load *, Account &'-'&IS_Period as Tbl3_Link

Resident Tbl3;

//Link Table.....................................................................................................................

Link_Table:

Load

IS_Period as CalendarDate,

Account,

'Tbl1' as Source,

Account &'-'&IS_Period as Tbl1_Link

Resident NT1;

Concatenate

Load

IS_Period as CalendarDate,

Account,

'Tbl2' as Source,

Account &'-'&IS_Period as Tbl2_Link

Resident NT2;

Concatenate

Load

IS_Period as CalendarDate,

Account,

'Tbl3' as Source,

Account &'-'&IS_Period as Tbl3_Link

Resident NT3;

Drop Fields IS_Period, Account from NT1;

Drop Fields IS_Period, Account from NT2;

Drop Fields IS_Period, Account from NT3;

Drop Table Tbl1;

Drop Table Tbl2;

Drop Table Tbl3;

//Master Calendar..............................................................................................

Calendar:

LOAD

Date(CalendarDate,'MM/DD/YYYY') AS CalendarDate,

Day(CalendarDate) AS CalendarDayOfMonth,

WeekDay(CalendarDate) AS CalendarDayName,

Week(CalendarDate) AS CalendarWeek,

Month(CalendarDate) AS CalendarMonth,

'Q' & Ceil(Month(CalendarDate)/3) AS CalendarQuarter,

Year(CalendarDate) AS CalendarYear

Resident Link_Table;

//Hope this will resolved your problem.

Regards,

Nirav Bhimani

Combining periods of different Income statements