Hi all, I been searching for a couple days and still haven't been able find the right answer. combine that with my limited knowledge of master files and segments, I really need some help, if only to point me at the right documents.

Lets say I have a file with the following fields in a single row:
company
division
style
sizescale1
sizescale2
sizescale3
color01
color02
...
...
color40

what I want to get from this is
company division style sizescale1 color01
company division style sizescale1 color02
...
...
company division style sizescale2 color01
company division style sizescale2 color02
...
...
company division style sizescale3 color01
etc

i.e. I need separate rows for each size scale field, and color field combination, which will covert each row to up to 120 rows (blank fields wouldn't need to be converted but that I can filter after the result are returned.

I tried using SQL create each field combination and then joined them through unions, but A.it was SLOOOOWWWW, and B.it creates a view with table count of 120, which I would have to join to a second table with a similar transpose problem (10 repeated fields), so my math gives a table count total of 1200 which is way past the DB2 SQL table limit for views, selects, etc.

Any ideas?

This message has been edited. Last edited by: Lloyd,

Hi Lloyd,

Just off the top of my head, try something like this -

MATCH FILE yourfile
PRINT color01
.....
   BY company
   BY division
   BY style
   BY sizescale01 AS sizescale
RUN
FILE yourfile
PRINT color01
.....
   BY company
   BY division
   BY style
   BY sizescale02 AS sizescale
AFTER MATCH HOLD AS yourhold OLD-OR-NEW
RUN
FILE yourfile
PRINT color01
.....
   BY company
   BY division
   BY style
   BY sizescale03 AS sizescale
AFTER MATCH HOLD AS yourhold OLD-OR-NEW
END

Good luck

T

here's a convoluted alternative in case Tony's good suggestion doesn't work. It requires multiple passes: First step separates the sizes and colours into two files. Then I use a master created on-the-fly that contains the OCCURS statement (the master could be previously existing). Then I create FOCUS databases and use a conditional join to join them on the key fields. This seems to work, but is convoluted.

-SET &ECHO=ALL;

SET HOLDLIST=PRINTONLY
SET BYDISPLA=ON
SET HOLDFORMAT=ALPHA
-RUN

FILEDEF MASTER DISK lloyd1.mas
-RUN

-WRITE MASTER FILE=LLOYD1, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=LLOYD1, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=COMPANY, ALIAS=COMPANY, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=DIVISION, ALIAS=DIVISION, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=STYLE, ALIAS=STYLE, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=SIZESCALE1, ALIAS=SIZESCALE1, USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=SIZESCALE2, ALIAS=SIZESCALE2, USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=SIZESCALE3, ALIAS=SIZESCALE3, USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=COLOR01, ALIAS=COLOR01, USAGE=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=COLOR02, ALIAS=COLOR02, USAGE=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=COLOR03, ALIAS=COLOR03, USAGE=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=COLOR04, ALIAS=COLOR04, USAGE=A10, ACTUAL=A10, $
-WRITE MASTER FIELDNAME=COLOR05, ALIAS=COLOR05, USAGE=A10, ACTUAL=A10, $

FILEDEF LLOYD1 DISK lloyd1.txt
-RUN

-WRITE LLOYD1 AA1101SMLRED       BLUE      YELLOW    GREEN     ORANGE
-WRITE LLOYD1 BB1202MLXPURPLE    BROWN     BLACK     SEPIA

TABLE FILE LLOYD1
PRINT
SIZESCALE1
SIZESCALE2
SIZESCALE3
BY COMPANY
BY DIVISION
BY STYLE
ON TABLE HOLD AS HSIZES FORMAT ALPHA
END
-RUN

TABLE FILE LLOYD1
PRINT
COLOR01
COLOR02
COLOR03
COLOR04
COLOR05
BY COMPANY
BY DIVISION
BY STYLE

ON TABLE HOLD AS HCOLORS FORMAT ALPHA
END
-RUN

FILEDEF MASTER DISK HSIZESA.mas
-RUN

-WRITE MASTER FILE=HSIZESA, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=HSIZESA, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=COMPANY, ALIAS=COMPANY, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=DIVISION, ALIAS=DIVISION, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=STYLE, ALIAS=STYLE, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=SIZESCALES, ALIAS=SIZESCALES, USAGE=A03, ACTUAL=A03, $

-WRITE MASTER SEGNAME=SIZESCALE_SEG, PARENT=HSIZESA, POSITION=SIZESCALES, OCCURS=3, $
-WRITE MASTER FIELDNAME=SIZESCALE, ALIAS=SIZESCALE, USAGE=A01, ACTUAL=A01, $
-CLOSE MASTER

FILEDEF MASTER DISK HCOLORSA.mas
-RUN

-WRITE MASTER FILE=HCOLORSA, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=HCOLORSA, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=COMPANY, ALIAS=COMPANY, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=DIVISION, ALIAS=DIVISION, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=STYLE, ALIAS=STYLE, USAGE=A02, ACTUAL=A02, $
-WRITE MASTER FIELDNAME=COLORS, ALIAS=COLORS, USAGE=A50, ACTUAL=A50, $

-WRITE MASTER SEGNAME=COLOR_SEG, PARENT=HCOLORSA, POSITION=COLORS, OCCURS=5, $
-WRITE MASTER FIELDNAME=COLOR, ALIAS=COLOR, USAGE=A10, ACTUAL=A10, $
-CLOSE MASTER

FILEDEF HSIZESA  DISK HSIZES.ftm
FILEDEF HCOLORSA DISK HCOLORS.ftm
-RUN

TABLE FILE HSIZESA
PRINT
SIZESCALE
BY COMPANY
BY DIVISION
BY STYLE
ON TABLE HOLD AS HSIZESB FORMAT FOCUS
END
-RUN

TABLE FILE HCOLORSA
PRINT
COLOR
BY COMPANY
BY DIVISION
BY STYLE
ON TABLE HOLD AS HCOLORSB FORMAT FOCUS
END
-RUN

JOIN FILE HSIZESB  AT SIZESCALE TAG S1 TO ALL
     FILE HCOLORSB AT COLOR     TAG C1 AS J1
  WHERE S1.COMPANY EQ C1.COMPANY;
  WHERE S1.DIVISION EQ C1.DIVISION;
  WHERE S1.STYLE EQ C1.STYLE;
END
-RUN

? JOIN
-RUN

TABLE FILE HSIZESB
PRINT
SIZESCALE
COLOR
BY COMPANY
BY DIVISION
BY STYLE
END

Now for lunch, which should be easier than this.

FIXFORM with grouping can perform the transposition. You still need some mechanism to get the cross-product of the size and color lists for each style.

I would load up and report from an appropriately shaped Focus file: 3 segments: style, size, color -- with style as parent of the other two.

There is a SET (CARTESIAN=ON?) to allow table to generate the cross-product of the two child segments.

• build the Focus master (offline, or using EX -LINES);
  
• have the fex issue a CREATE
  
• pull the HOLD file as you described it
  
• take your HOLD file and use it as DATA, in a Modify, to populate the Style (root) and Size (first child) segments. (Hand code the FIXFORM to handle the three Size columns as three occurrences of a one-column group, and skip the 40 color columns)
  
• use the same HOLD file as DATA in a second Modify, to match the Style (root) segment keys and populate the Color (2nd child) segment instances. (skip the three size columns, and handle the 40 color columns as groups of one column with count of 40)
  
• TABLE to get your report, with the appropriate SET to generate cross-products.
  

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

Lloyd,

Keep it simple.
Here is a short example:
Your file in a single row contains fields
CDS (an abbreviation of company, division, styel)
SCALESIZE (repeated 3 times)
COLOR (repeated 4 times)
The MASTER:

  
FILENAME=lloyd, SUFFIX=FIX     ,
 DATASET=FOCALPOINT/LLOYD.TXT, $
  SEGMENT=LLOYD, SEGTYPE=S0, $
    FIELDNAME=CDS, USAGE=A3, ACTUAL=A3, $
  SEGMENT=SCALE, SEGTYPE=S0, PARENT=LLOYD, OCCURS=3, $
    FIELDNAME=SCALESIZE, USAGE=I2, ACTUAL=A1, $
  SEGMENT=COLOR, SEGTYPE=S0, PARENT=LLOYD, OCCURS=4, $
    FIELDNAME=COLOR, USAGE=A1, ACTUAL=A1, $

The Data:

  
111123abcd
222456efgh
333246adgj
444357zcbm

The code:

  
SET CARTESIAN = ON
TABLE FILE LLOYD
PRINT
     SCALESIZE
     COLOR
BY  CDS
END

Lots of info here to digest..going to try them and let you guys know my results.

Danny..one thing I need to mention is that this is a DB file on the iseries, so I'm not sure how that affects you solution.

The essential issue here is how to take multiple fields and make them appear in a single column - i.e. as a single field.

Francis' solution using OCCURS is exactly what occurred (sorry!) to me right off the top.

If the number of sizescales and colours is variable, then you'll need to count them which implies more than one pass through the data.
For example, I do a first pass on inventory data and count the maximum number of bin locations for a given product and then do a SAVE (NOT a HOLD) of the output. Then use this [snippet of] a master to do further processing.

SUGAR      1   WJ11  2   WJ16   3   WJ24   4   WK08    5   WK14 

The result is that I get a variable number of bin locations (fieldnames/columns) to appear as a single fieldname/column.

FILENAME=inv2comp2, SUFFIX=FIX     , $
  SEGMENT=MAIN, SEGTYPE=S1, $
    FIELDNAME=ITEM_ID, ALIAS=E01, USAGE=A25, ACTUAL=A25,      MISSING=ON, $
    FIELDNAME=MAXNUM, ALIAS=E03, USAGE=I2, ACTUAL=A02, $
  SEGMENT=BINREC, SEGTYPE=S1, PARENT=MAIN, OCCURS=MAXNUM, $
    FIELDNAME=BINNUM, ALIAS=E04, USAGE=I2, ACTUAL=A02,      MISSING=ON, $
 

** Just in case it isn't obvious, I do a SAVE because I do NOT want to create a new master, which would happen if I used a HOLD - and that HOLD would have all the same fields as the original, thereby accomplishing exactly nothing.

This message has been edited. Last edited by: George Patton,

All, I think I get most of this and have made good progress, but It's still not 100% what I am trying to get.

As you can see below, the second(07) and third(13) size scales isn't picking up the color fields. Below is the master file I created, I tried setting the the third segment(with the colors as a child of seg2, but it wouldn't let me, gave me an error so I don't thinks I'm doing right. Or do I have to do as J.gross advises and generate two files and then do a cross product?

I hope there is a different way, because I already have to take this file and join it to the actual size master, which can be another 10-30 records depending on whether all size scales are being used or not.

 
CPY007	DIV007	KYS007	SEY007	CC#007	SST007	         STD007	         SSC007	SSD007	SSE007	SIZESCALE	COLOR
1	15	S	12	345424	KS23419TG8	CARGO PANT	05	07	13	05	260
1	15	S	12	345424	KS23419TG8	CARGO PANT	05	07	13	07	.
1	15	S	12	345424	KS23419TG8	CARGO PANT	05	07	13	13	.
1	15	S	13	724425	KS390335A5	MODERN FIT JEAN	05	07	13	05	966
1	15	S	13	724425	KS390335A5	MODERN FIT JEAN	05	07	13	07	.
1	15	S	13	724425	KS390335A5	MODERN FIT JEAN	05	07	13	13	.

  
FILENAME=edi264, SUFFIX=SQLODBC , $
  SEGMENT=PST007, SEGTYPE=S0, $
    FIELDNAME=CPY007, ALIAS=CPY007, USAGE=P3, ACTUAL=P2, $
    FIELDNAME=DIV007, ALIAS=DIV007, USAGE=P3, ACTUAL=P2, $
    FIELDNAME=SEY007, ALIAS=SEY007, USAGE=P3, ACTUAL=P2, $
    FIELDNAME=KYS007, ALIAS=KYS007, USAGE=A1, ACTUAL=A1, $
    FIELDNAME=SST007, ALIAS=SST007, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=CC#007, ALIAS=CC#007, USAGE=P8, ACTUAL=P4, $
    FIELDNAME=SST007, ALIAS=SST007, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=STD007, ALIAS=STD007, USAGE=A25, ACTUAL=A25, $
    FIELDNAME=SSC007, ALIAS=SSC007, USAGE=A2, ACTUAL=A2, $
    FIELDNAME=SSD007, ALIAS=SSD007, USAGE=A2, ACTUAL=A2, $
    FIELDNAME=SSE007, ALIAS=SSE007, USAGE=A2, ACTUAL=A2, $
    FIELDNAME=CL0107, ALIAS=CL0107, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0207, ALIAS=CL0207, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0307, ALIAS=CL0307, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0407, ALIAS=CL0407, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0507, ALIAS=CL0507, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0607, ALIAS=CL0607, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0707, ALIAS=CL0707, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0807, ALIAS=CL0807, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL0907, ALIAS=CL0907, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1007, ALIAS=CL1007, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1107, ALIAS=CL1107, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1207, ALIAS=CL1207, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1307, ALIAS=CL1307, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1407, ALIAS=CL1407, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1507, ALIAS=CL1507, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1607, ALIAS=CL1607, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1707, ALIAS=CL1707, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1807, ALIAS=CL1807, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL1907, ALIAS=CL1907, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2007, ALIAS=CL2007, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2107, ALIAS=CL2107, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2207, ALIAS=CL2207, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2307, ALIAS=CL2307, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2407, ALIAS=CL2407, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2507, ALIAS=CL2507, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2607, ALIAS=CL2607, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2707, ALIAS=CL2707, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2807, ALIAS=CL2807, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL2907, ALIAS=CL2907, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3007, ALIAS=CL3007, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3107, ALIAS=CL3107, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3207, ALIAS=CL3207, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3307, ALIAS=CL3307, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3407, ALIAS=CL3407, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3507, ALIAS=CL3507, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3607, ALIAS=CL3607, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3707, ALIAS=CL3707, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3807, ALIAS=CL3807, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL3907, ALIAS=CL3907, USAGE=A5, ACTUAL=A5, $
    FIELDNAME=CL4007, ALIAS=CL4007, USAGE=A5, ACTUAL=A5, $
  SEGMENT=SEG1, SEGTYPE=S0, PARENT=PST007, OCCURS=3, POSITION=SSC007, $
    FIELDNAME=SIZESCALE, USAGE=A2, ACTUAL=A2, $
  SEGMENT=SEG2, SEGTYPE=S0, PARENT=PST007, OCCURS=40, POSITION=CL0107, $
    FIELDNAME=COLOR, ALIAS=CL0307, USAGE=A5, ACTUAL=A5, $

This message has been edited. Last edited by: Lloyd,

When using the OCCURS clause, the POSITION must point to a field that contains ALL of the values, not just the first value. You should have a field called something like CL07 that is 200 chars (40 * 5)...

Lloyd --

Are you attempting to use that MFD (with SUFFIX=SQLODBC) both to pull the data, and to produce your report? No one suggested combining the two.

You presumably already have a synonym for pulling the data.

Based on the documentation for OCCURS and POSITION, my impression is the MFD containing the OCCURS should have SUFFIX=FIX (i.e., describe the HOLD (or SAVE) file returned by the database server)

If that's the case, then you can simplify its root segment: declare the stretch containing the 3 occurrences of SSCxxx/A2 (sizescale) as a single A6 field. Similarly, declare the 40 A5 color fields as an A200.

Then your lower two segments would redefine those two blocks.

You may also need to use SEGTYPE=U.

Let us know how it works out.

What I was attempting to do was use this to pull the data and then combine it with other tables to generate the report. Now, I get what you are saying though. I have to first pull the original data down and save it, and then apply my new master file to transpose that data (from the hold/save file)?

I'm getting there.....

Thanks everyone for all your help. My final solution was to create two masters based on my bas DB file on the Iseries. One to break out all the size scales, and another to break out the colors codes. I did a similar process to break out the size codes. I then joined all of these with SET CARTESIAN=ON.

Now I have individual records for each size scale, size, and color combination in my table which is what I need for my report.

Thanks again everyone.

Yay ! The fogeys all end up on the same page. Amazing.

Hey, stop yelling kid! I'm taking my afternoon nap.