Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

henster36
New Contributor III

Combining periods of different Income statements

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]);

3 Replies
nirav_bhimani
Contributor III

Re: Combining periods of different Income statements

Hi,

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

Regards,

Nirav Bhimani

henster36
New Contributor III

Re: Combining periods of different Income statements

Hi Nariv

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

nirav_bhimani
Contributor III

Re: Combining periods of different Income statements

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

Community Browser