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  iWay Software Product Forum on Focal Point    Data Migrator - Input 1 year table row with 12 months to 12 rows in output table
Go
New
Search
Notify
Tools
Reply
  
Data Migrator - Input 1 year table row with 12 months to 12 rows in output table
 Login/Join
 
Member
posted
Our input table has 12 columns, one for each month, with a unit count in it. I need in our ouput table to turn that into 12 rows, one for each month.

What is the best way to accomplish this?

Example.

Zone,uJan,uFeb,uMar,uApr,uMay,uJun,uJul,uAug,uSep,uOct,uNov,uDec
Z10,5,8,6,7,9,2,5,7,3,9,4,3



I need to get that row outputed to:

Zone,Month,Units
Z10,1,5
Z10,2,8
Z10,3,6
Z10,4,7
Z10,5,9
Z10,6,2
Z10,7,5
Z10,8,7
Z10,9,3
Z10,10,9
Z10,11,4
Z10,12,3
 
Posts: 1 | Location: Central IL | Registered: July 12, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Are you trying to do this via the GUI? If so, have you tried a UNION? I don't know if it'll take it, but you could try typing this directly int the SQL:
SELECT
ZONE,
'1' AS MONTH,
UJAN AS UNITS
FROM
UNION
SELECT
ZONE,
'2' AS MONTH,
UFEB AS UNITS
FROM
UNION
.
.
.

etc.

If it won't allow you to do this (and I suspect it won't), are you open to creating a Data Migrator stored procedure? That way, you can use plain old FOCUS, hold off the results, then use the hold file as the source in your data flow. If you're interested, let me know your email address and I'll send you a write up on how to do that. That's actually what I do most of the time. I do very little via the GUI.

As for how to do this with FOCUS, I have seen several write ups on this sort of thing. I believe the McGuyver technique was created just for this sort of thing. And there is always MATCH, but that can be inefficient depending on your data. Or, if your source is an RDBMS table, you could do a SQL pass-thru and still do a UNION.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
You could indeed create a DBMS SQL flow and do a UNION. It's a lot of typing tho, you need a dozen select statements one for each month.

If a FOCUS programmer should read this entry they will surely start blabbering about that 1980's TV show.

But there is a simple DataMigrator solution using another standard SQL construct however, the CROSS JOIN.

Create a synonym with just one column:
FILENAME=mnths, SUFFIX=SQLMSS , $
SEGMENT=MNTHS, SEGTYPE=S0, $
FIELDNAME=MNTH, ALIAS=MNTH, USAGE=I5, ACTUAL=I2, $

Then use the DMC to create the table and to insert twelve (12) rows of data. That gets us the numbers 1 through 12.

Next create a new flow and join the source table to the months table and select a join type of CROSS JOIN which doesn't require any join conditions.

That gives us a so-called cartesian product, every possible combination of the rows from the right and left tables. So with 12 months, if there are two rows in your source, we will get back 12 x 2 or 24 rows, one for each month for each source row.

So now you can simply select ZONE and MNTH. For UNITS we need to specify which month's units to use and that can be done with a CASE statement which you can actually type into the calculator:

CASE
WHEN T2.MNTH = 1 THEN JAN
WHEN T2.MNTH = 2 THEN FEB
WHEN T2.MNTH = 3 THEN MAR
WHEN T2.MNTH = 4 THEN APR
WHEN T2.MNTH = 5 THEN MAY
WHEN T2.MNTH = 6 THEN JUN
WHEN T2.MNTH = 7 THEN JUL
WHEN T2.MNTH = 8 THEN AUG
WHEN T2.MNTH = 9 THEN SEP
WHEN T2.MNTH = 10 THEN OCT
WHEN T2.MNTH = 11 THEN NOV
WHEN T2.MNTH = 12 THEN DEC
END

Here I assumed that the column names were the three letter month abbreviations.

This message has been edited. Last edited by: Clif,
 
Posts: 382 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
You didn't mention what kind of table it is but if you can make it be a fixed file, you can use an alternate master with an OCCURS in it to do exactly what you want.

FILENAME=XXXX, SUFFIX=FIX , $
SEGNAME=ZONE, SEGTYPE=S1, $
FIELDNAME=ZONE, ALIAS=ZONE, USAGE=A3, ACTUAL=A3, $
SEGNAME=MONTHS,SEGTYPE=S0,PARENT=ZONE,OCCURS=12,$
FIELDNAME=UNITS,ALIAS=UNITS,USAGE=I4,ACTUAL=I4,$
FIELDNAME=MOCNTR,ALIAS=ORDER,USAGE=I4,ACTUAL=I4,$


The ALIAS=ORDER field is a virtual counter that will match up with the month.

Simply FILEDEF the file or extract to this master and say (can convert this to SQL):

TABLE FILE XXX
PRINT UNITS
BY ZONE
BY MONCTR AS MONTH
END


This is a very old technique that no one uses anymore (but me, I guess) but can be extremely valuable in the right situation.


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
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator - Input 1 year table row with 12 months to 12 rows in output table

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