topic CROSSTABLE question in QlikView

CROSSTABLE question

alexis — Fri, 12 Jun 2015 17:43:20 GMT

I have the following table (Master_Temp) that contains monthly values for the whole year (see fields [01] through to [12)). As well as that there is another field called "OpeningBalance" that needs to be added to the sum of the monthly figures to work out the total.

Master_Temp:

LOAD

Year,

AccountNo,

RecordType,

Period,

OpeningBalance,

[01], //Jan,

[02], //Feb,

[03], //Mar,

[04], //Apr,

[05], //May,

[06], //Jun,

[07], //Jul,

[08], //Aug,

[09], //Sep,

[10], //Oct,

[11], //Nov,

[12] //Dec

FROM

Data\QVD\GLT0.QVD

(qvd)

;

I used CROSSTABLE (see below) to create individual rows for each month.

In calculating the Year Total I cannot do SUM(Master_Amount) + SUM(OpeningBalance)

because "OpeningBalance" will be added multiple times, so your help is required:

a) How do I transfer just a single instance of "OpeningBalance" in the set (say for example to pair it with January - [01]) or

b) Is there another way to take into consideration just a single instance of OpeningBalance?

Thanks in advance

Alexis

Master:

CROSSTABLE (Master_Month, Master_Amount, 5) LOAD

Year,

AccountNo,

RecordType,

Period,

OpeningBalance,

[01], //Jan,

[02], //Feb,

[03], //Mar,

[04], //Apr,

[05], //May,

[06], //Jun,

[07], //Jul,

[08], //Aug,

[09], //Sep,

[10], //Oct,

[11], //Nov,

[12] //Dec

Resident Master_Temp

;

drop table Master_Temp;

Re: CROSSTABLE question

MarcoWedel — Fri, 12 Jun 2015 22:09:45 GMT

Hi,

one solution could be:

Master_Temp:

CROSSTABLE (MasterMonthtemp, Master_Amount, 4)

LOAD * INLINE [

Year, AccountNo, RecordType, Period, OpeningBalance, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12

2014, Account1, RecordType1, Period1, 50, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 1100, 1200

2014, Account2, RecordType1, Period1, 60, 110, 220, 330, 440, 550, 660, 770, 880, 990, 1010, 1120, 1230

2015, Account1, RecordType1, Period1, 70, 101, 202, 303, 404, 505, 606, 707, 808, 909, 1011, 1122, 1233

2015, Account2, RecordType1, Period1, 80, 111, 222, 333, 444, 555, 666, 777, 888, 999, 1012, 1123, 1234

];

Left Join (Master_Temp)

LOAD Distinct

MasterMonthtemp,

If(MasterMonthtemp='OpeningBalance',Dual('OpeningBalance',0),Month(Date#(MasterMonthtemp,'MM'))) as Master_Month

Resident Master_Temp;

DROP Field MasterMonthtemp;

hope this helps

regards

Marco

Re: CROSSTABLE question

MarcoWedel — Fri, 12 Jun 2015 22:11:50 GMT

on the other hand, you could as well stick to your data model and try with

SUM(Master_Amount) + Only(OpeningBalance)

or just

SUM(Master_Amount) + OpeningBalance

instead of

SUM(Master_Amount) + SUM(OpeningBalance)

hope this helps

regards

Marco

Re: CROSSTABLE question

alexis — Tue, 23 Jun 2015 09:46:54 GMT

Dear Marco

Thank you very much for responding to my question and please accept my apologies for the late acknowledgement of your effort - you know how things are when priorities change and deadlines approach!!

Based on your example, it gave me the idea to solve the problem as follows:

Master_Temp:

LOAD

Year,

AccountNo,

RecordType,

Period,

OpeningBalance,

[01], //Jan,

[02], //Feb,

[03], //Mar,

[04], //Apr,

[05], //May,

[06], //Jun,

[07], //Jul,

[08], //Aug,

[09], //Sep,

[10], //Oct,

[11], //Nov,

[12] //Dec

FROM

Data\QVD\GLT0.QVD

(qvd)

;

Master_Temp2:

CROSSTABLE (Master_Month, Master_Amount, 5) LOAD

Year,

AccountNo,

RecordType,

Period,

OpeningBalance,

[01], //Jan,

[02], //Feb,

[03], //Mar,

[04], //Apr,

[05], //May,

[06], //Jun,

[07], //Jul,

[08], //Aug,

[09], //Sep,

[10], //Oct,

[11], //Nov,

[12] //Dec

RESIDENT Master_Temp

;

DROP TABLE Master_Temp;

Master:

LOAD

Year,

AccountNo,

RecordType,

Period,

Month(Date#(Master_Month,'MM')) As Mast_Month,

num(Master_Amount) As Mast_Amount,

if(Master_Month='01',

OpeningBalance,

0) As OpeningBalance

RESIDENT Master_Temp2;

DROP TABLE Master_Temp2;