Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Find attached Am looking for how to group my expression in years and to get the totalled for the defined totals. see attached
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;
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;