CROSSTABLE question

[alexis]

Hi

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;

[MarcoWedel]

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

[MarcoWedel]

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

[alexis]

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;