How to get Total for defined fields

[#1]

Find attached Am looking for how to group my expression in years and to get the totalled for the defined totals. see attached

[sunny_talwar · Accepted solution]

May be this:

T:

LOAD No,

    Text,

    Year,

    Months,

    Data

FROM

[0215_184014.xls]

(biff, embedded labels, table is Sheet1$);

STORE T into T.qvd(qvd);

NoConcatenate

Final:

Load *,

  1 as SORT

Resident T

Where WildMatch(No, '*14*', '*20*', '*21*','*22*', '*36*','*24*');

Concatenate(Final)

Load Dual ('Total equipment:',1) as Text,

  Year,

  Sum(Data) as Data,

  2 as SORT

RESIDENT T

where WildMatch(No, '*14*', '*20*', '*21*','*22*', '*36*','*24*')

Group By Year;

Concatenate(Final)

Load *,

  3 as SORT

Resident T

Where WildMatch(No, '*26*', '*27*', '*28*');

Concatenate(Final)

Load Dual ('Total Work:',2) as Text,

  Sum(Data) as Data,

  Year,

  4 as SORT

RESIDENT T

where wildmatch(No, '*26*', '*27*', '*28*')

Group By Year;

Concatenate(Final)

Load *,

  5 as SORT

Resident T

Where WildMatch(No, '*26*', '*27*', '*28*', '*14*', '*20*', '*21*','*22*', '*36*','*24*');

Concatenate(Final)

Load Dual ('TOTAL:',3) as Text,

  Sum(Data) as Data,

  Year,

  6 as SORT

RESIDENT T

Where WildMatch(No, '*26*', '*27*', '*28*', '*14*', '*20*', '*21*','*22*', '*36*','*24*')

Group By Year;

Drop Table T;