Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to transpose Row containing multiple values to multiple Row's
Go
New
Search
Notify
Tools
Reply
  
How to transpose Row containing multiple values to multiple Row's
 Login/Join
 
Member
posted
Hi,

I have data in a table named Number that is stored in a field named Account as

{number:12345}
{number:23456}
{number:34567} {number:45678}
{number:56789} {number:01234} {number:11234}
{number:11345}
{number:11456}

I need to create a HOLD file as

12345
23456
34567
45678
56789
01234
11234
11345
11456

I am not sure how to take the rows that contain multiple numbers and convert them to multiple rows in the hold file.

i.e take

{number:34567} {number:45678}

and make it

34567
45678

Thanks,

Zippo
 
Posts: 12 | Location: New Yawk | Registered: February 16, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
What kind of table is it? Fixed, relational,? For fixed format, you could define the master with an OCCURS.

For other file types, you can TABLE the data as such:

DEFINE FILE FILE1
ONE/I1=1;
TWO/I1=2;
THREE/I1=3;
END
TABLE FILE FILE1
PRINT ONE COL1NO TWO COL2NO THREE COL3NO
BY WHATEVER
ON TABLE HOLD FORMAT ALPHA
END

Then create another master using OCCURS, filedef the hold file to it. Then you can table it vertically.

Also look up the MacGyver technique which might be useful here.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
Zippo

Is the data below in one field or three seperate fields? If it is three seperate fields are the formats of all three fields the same?

{number:56789} {number:01234} {number:11234} 


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Also, please update your profile signature with your product suite, releases and platform as Glenda and I have done. That makes it easier for us to help you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
I think you can only do this using multiple passes, though someone cleverer may have a better idea:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA

FILEDEF DATAMAST DISK NUMBER.MAS
-RUN

-WRITE DATAMAST
-WRITE DATAMAST FILE=NUMBER, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=ACCOUNT, ALIAS=E01, USAGE=A50, ACTUAL=A50, $

FILEDEF NUMBER DISK NUMBER.FTM
-RUN

-WRITE NUMBER {number:12345}
-WRITE NUMBER {number:23456}
-WRITE NUMBER {number:34567} {number:45678}
-WRITE NUMBER {number:56789} {number:01234} {number:11234}
-WRITE NUMBER {number:11345}
-WRITE NUMBER {number:11456}
-RUN

DEFINE FILE NUMBER
ACCOUNT1/A5 WITH ACCOUNT = SUBSTR(60, ACCOUNT,  9, 13, 5, 'A5');
ACCOUNT2/A5 WITH ACCOUNT = SUBSTR(60, ACCOUNT, 24, 28, 5, 'A5');
ACCOUNT3/A5 WITH ACCOUNT = SUBSTR(60, ACCOUNT, 39, 43, 5, 'A5');
END
-RUN

TABLE FILE NUMBER
PRINT
ACCOUNT1 AS ACCOUNT
ON TABLE HOLD AS HACCOUNT1
END
-RUN

TABLE FILE NUMBER
PRINT
ACCOUNT2 AS ACCOUNT
ON TABLE HOLD AS HACCOUNT2
WHERE ACCOUNT2 NE ''
END
-RUN

TABLE FILE NUMBER
PRINT
ACCOUNT3 AS ACCOUNT
ON TABLE HOLD AS HACCOUNT3
WHERE ACCOUNT3 NE ''
END
-RUN

TABLE FILE HACCOUNT1
PRINT ACCOUNT
BY ACCOUNT NOPRINT
ON TABLE HOLD AS HACCOUNT
MORE
FILE HACCOUNT2
MORE
FILE HACCOUNT3
END
-RUN

TABLE FILE HACCOUNT
PRINT *
END
-RUN


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Zippo,

The code Frances sent will also work if the data is already in separate fields as long as the fields are formatted the same. If not, then you use a defined field to make them the same.

Francis,

You are supposed to be relaxing and having fun this week. But it's always nice to hear from you. Winky


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
I'm sorry, Francis, but I couldn't resist especially after you created the master and data:

 FILEDEF DATAMAST DISK number.mas
-RUN

-WRITE DATAMAST
-WRITE DATAMAST FILE=NUMBER, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, OCCURS=VARIABLE,$
-WRITE DATAMAST FIELD=NUMBER, ALIAS=E01, USAGE=A08, ACTUAL=A08, $
-WRITE DATAMAST FIELD=ACCOUNT, ALIAS=E02, USAGE=A5, ACTUAL=A5, $
-WRITE DATAMAST FIELD= , ALIAS=E03, USAGE=A2, ACTUAL=A2, $
-*-WRITE DATAMAST FIELD=ACCTCNT, ALIAS=ORDER, USAGE=I8, ACTUAL=I4, $

FILEDEF NUMBER DISK number.ftm
-RUN

-WRITE NUMBER {number:12345}
-WRITE NUMBER {number:23456}
-WRITE NUMBER {number:34567} {number:45678}
-WRITE NUMBER {number:56789} {number:01234} {number:11234}
-WRITE NUMBER {number:11345}
-WRITE NUMBER {number:11456}

TABLE FILE NUMBER
PRINT
ACCOUNT
BY NUMBER NOPRINT
END
 


I couldn't get it to work with the ORDER field.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Ginny,

This works very well:
  
-* File Ginny2.fex
FILEDEF DATAMAST DISK number.mas
-RUN

-WRITE DATAMAST FILE=NUMBER, SUFFIX=FIX
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0
-WRITE DATAMAST FIELD=FILL, ALIAS=FF, USAGE=A1, ACTUAL=A1, $
-WRITE DATAMAST SEGNAME=NUMS, PARENT=SEG1, OCCURS=VARIABLE
-WRITE DATAMAST FIELD=NUMBER, ALIAS=NUM, USAGE=A5, ACTUAL=A5, $
-WRITE DATAMAST FIELD=ACCTCNT, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $
-RUN

FILEDEF NUMBER DISK number.ftm
-RUN

-WRITE NUMBER >12345
-WRITE NUMBER >23456
-WRITE NUMBER >3456745678
-WRITE NUMBER >567890123411234
-WRITE NUMBER >11345
-WRITE NUMBER >11456
-RUN

TABLE FILE NUMBER
PRINT
NUMBER ACCTCNT
END


Here is the output:

  
PAGE     1
  
  
  NUMBER  ACCTCNT                      
  ------  -------          
  12345         1
  23456         1
  34567         1
  45678         2
  56789         1
  01234         2
  11234         3
  11345         1
  11456         1



And thanks for the code!

Francis,
Are you at Summit?
I would like to meet you.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
if its the same field im sure there is a fucntion that allows you to take out parts of a field based on a delimiter, i used to have to do this with access vb so will have to look into the WF solution... although provided the numbers are always entered in the field the same way Francis' code should always work.


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
I couldn't make it to Summit this year, so I'm as hard at work as the rest of you.

I'm surprised that people who are at summit have time to respond on FocalPoint.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Based on the original post, the assumption is that the data is stored in one field of a table
quote:
I have data in a table ... that is stored in a field
, therefore I didn't use an occurs in a Master, since the Master would have already been created.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Sorry to hear you couldn't make it this year Francis. I'm sure they are all missing your presence.

As for them having time to respond, you know they are workaholic just like most of us. Big Grin


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Francis

quote:
I'm surprised that people who are at summit have time to respond on FocalPoint


We here get from some people the remark "I'm suprised that people who are at work find time to post on focalpoint.... Wink

From a sunny Nashville....

BTW I'm curious if Zippo got the answer he was looking for....
For me it does!




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
I'm suprised that people who are at work find time to post on focalpoint.... Wink


That's a good one!

Frank, are you enjoying yourself?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Yes it's great here, I found some time to relax by posting on focalpoint..... Big Grin

One of the best things here is shaking hand with all the focalpoint users you never see. Discovering that the man behind the nickname is a woman and the other way around.... Sweating




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Even if the data is in a single field, you could put it in a hold file and do an alternate master with an occurs in it. Once the data is in the hold file, it is just a giant string that you can parse anyway you like.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Member
posted Hide Post
I am glad I ran into this thread. I am looking for a solution for a similar problem.

I am using DataMigrator 764 and have a data flow which moves data from a source table (MSSQL) to a target table (MSSQL).

Both Source and target tables have two columns - Dept_ID and City.

Source table values are -

Dept_ID City
001 Chicago, Lisle, Naperville
002 Detroit, Southfield, Kalamazoo

I would like to load this data into my target table as

Dept_ID City
001 Chicago
001 Lisle
001 Naperville
002 Detroit
002 Southfield
002 Kalamazoo

How do I explode each single row in source into multiple rows in target? What function can I use to parse the field value if the delimiter is a carriage return instead of a comma?

Thanks.


iSM 616
iDM 7705
iDQC 802
WF 77
Windows 2008 Server
 
Posts: 29 | Registered: May 01, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
I've not tried this but look this up in Advanced Search using DFIX as the keyword:

quote:
Syntax: How to Define a File With Delimiters
Delimiters must be defined in the Master File. The FILE declaration must include the following attribute:

SUFFIX=DFIXDescribe the delimiter characters in a special field or group named DELIMITER. The delimiter characters are specified in the ALIAS attribute of this special field or group.

To use a delimiter that consists of a single non-printable character or of one or more printable characters, the delimiter is defined as a field with the following attributes:

FIELDNAME=DELIMITER, ALIAS=delimiter, USAGE=ufmt, ACTUAL=afmt ,$To use a delimiter that consists of multiple non-printable characters or a combination of printable and non-printable characters, the delimiter is defined as a group:

GROUP=DELIMITER, ALIAS= , USAGE=ufmtg, ACTUAL=afmtg ,$ FIELDNAME=DELIMITER, ALIAS=delimiter1, USAGE=ufmt1, ACTUAL=afmt1 ,$ . . . FIELDNAME=DELIMITER, ALIAS=delimitern, USAGE=ufmtn, ACTUAL=afmtn ,$where:

DELIMITER

Indicates that the field or group is used as the delimiter in the data source.


delimiter

Identifies a delimiter. For one or more printable characters, the value consists of the actual characters. The delimiter must be enclosed in single quotation marks if it includes characters used as delimiters in Master File syntax. For a non-printable character, the value is the decimal equivalent of the EBCDIC or ASCII representation of the character, depending on your operating environment.


ufmt, afmt

Are the USAGE and ACTUAL formats for the delimiter. Possible values are:



Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Member
posted Hide Post
All,

Thank you very much for your time and suggestions. In working with the owner of the mySQL DB it is clear the data needs to be cleaned. Once that is done I am sure the suggestions will work. Until then the data is such a mess I can't even test it.

Zippo
 
Posts: 12 | Location: New Yawk | Registered: February 16, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
AK,

I suggest you start another thread with your problem. Also, it would be helpful is you could indicate the format of the City field and the max number of cities per Dept.

See the new topic I started.

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


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Hi AK,

Here is the suggestion that Daniel provided:
http://forums.informationbuilders.com/eve/forums/a/tpc/...221072782#3221072782

Cheers, Smiler

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to transpose Row containing multiple values to multiple Row's

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.