Hi All,

I am hopeful there is a hidden simple solution for the below scenario.

Apologies for the text tables.

I have a table:
SHOP |DATE |SALES |PERCENT
Jims |201201 |10 |5%
Jims |201202 |15 |10%
Jims |201203 |11 |7%
Bobs |201201 |14 |9%
Bobs |201202 |5 |2%
Bobs |201203 |3 |1%
Andrews |201201 |20 |15%
Andrews |201202 |11 |7%
Andrews |201203 |17 |12%

Running the following statement:
TABLE FILE RESULTS
SUM
SALES
PERCENT
BY SHOP
ACROSS DATE
END

Gives me:
|201201 |201201 |201202 |201202 |201203 |201203
|SALES |PERCENT |SALES |PERCENT |SALES |PERCENT
Jims |10 |5% |15 |10% |11 |7%
Bobs |14 |9% |5 |2% |3 |1%
Andrews |20 |15% |11 |7% |17 |12%

Is there any way to write a single statement to give me:
|201201 |201202 |201203 |201201 |201202 |201203
|SALES |SALES |SALES |PERCENT |PERCENT |PERCENT
Jims |10 |15 |11 |5% |10% |7%
Bobs |14 |5 |3 |9% |2% |1%
Andrews |20 |11 |17 |15% |7% |12%

While I could pivot the sales and percent columns and perform a nested ACROSS statement this would cause issues with number formats. I was hopeful there is a way to maintain the number formats.

Many many thanks for any help.

Something like this (untested):

DEFINE FILE X              <== starting with 9 rows
  FIELD1/A12='SALES';
  VALUE1/D12=SALES;
  FIELD2/A12='PERCENT';
  VALUE2/D12=PERCENT;
END
MATCH FILE X
SUM
  FIELD1 AS FIELD
  VALUE1 AS VALUE
  BY SHOP
  BY DATE
RUN
FILE X
SUM
  FIELD2 AS FIELD
  VALUE2 AS VALUE
  BY SHOP
  BY DATE
AFTER MATCH HOLD OLD-OR-NEW        <== HOLD has 18 rows
END
DEFINE FILE HOLD
  FMT/A8= DECODE FIELD ('SALES' 'D12' ELSE 'D12%');  <== dynamic format
END

TABLE FILE HOLD
SUM
VALUE/FMT AS ''
ACROSS HIGHEST FIELD NOPRINT  <== sorting on FIELDS, to group the SALES rows followed by the PERCENT rows
ACROSS DATE AS ''             <== and on date within that
ACROSS FIELD AS ''            <== just to show SALES or PERCENT under date in each column
BY SHOP
END

Cube,

Assumption concerning the formats of your fields:
SHOP/A12
DATE/A6
SALES/I6
PERCENT/I3%

Assumption concerning file RESULTS: there is 1 record per SHOP|DATE.

Proposed solution:

DEFINE FILE RESULTS
ASALES/A6=FPRINT(SALES, 'I6', 'A6');
APCT/A6=FPRINT(PERCENT, 'I5%', 'A6');
END

TABLE FILE RESULTS
PRINT ASALES APCT
BY SHOP BY DATE
ON TABLE SAVE AS HRESULTS
END
-RUN
EX -LINES 8 EDAPUT MASTER, HRESULTS, C, MEM
FILENAME=HRESULTS, SUFFIX=FIX
 SEGNAME=HRESULTS, SEGTYPE=S0
  FIELDNAME=SHOP, ALIAS=SHOP, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=DATE, ALIAS=DATE, USAGE=A6, ACTUAL=A6, $
 SEGNAME=FIELDS, SEGTYPE=S0, PARENT=HRESULTS, OCCURS=VARIABLE
  FIELDNAME=RESULT, ALIAS=RESULT, USAGE=A6, ACTUAL=A6, $
  FIELDNAME=POS, ALIAS=ORDER, USAGE=I2, ACTUAL=I4, $
-RUN
DEFINE FILE HRESULTS
APOS/A8=IF POS EQ 1 THEN 'SALES' ELSE 'PERCENT';
END
TABLE FILE HRESULTS 
SUM RESULT
BY SHOP
ACROSS POS NOPRINT
ACROSS DATE
ACROSS APOS
END

This message has been edited. Last edited by: Danny-SRL,

Hi Jack,

More than 1 way to skin a... fex!

Awesome guys. Thanks.

I didn't realise you could dynamically set the format.

This will be a big help.

!ובלי להזכיר מקגייבר

[translation: And neither of us so much as mentioned McGuyver]

This message has been edited. Last edited by: j.gross,

אכן כן, יעקב
?אגב, איך יודעים ש-3 תפילות ביום מקורן מהאבות