Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Create multiple rows from single value

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Create multiple rows from single value
 Login/Join
 
Member
posted
I am looking for a way to create multiple rows for a single value of a field. I thought some sort of decode function would work but it is only reading the first decode.

I will provide an example.

If I have fieldA with values

RED
YELLOW
GREEN
BLACK

and fieldB with values
1
2
3
4

What I am trying to do is have the following print.

FieldA FieldB DefineFieldA
RED 1 Red - Urgent
RED 1 Red - Medium
RED 1 Red - Low
YELLOW 2 Yellow - Urgent
YELLOW 2 Yellow - Medium
GREEN 3 Green
BLACK 4 Black


So I start with 4 values for fieldA and would like to end up with 7.

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


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
Virtuoso
posted Hide Post
Are these fields in one or in two tables?
And if in two, is there a relation between them?




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, 2006Report This Post
Member
posted Hide Post
FieldA and FieldB are in the same table. The definefieldA is a created designation to fieldA.

Does that make sense?


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
Platinum Member
posted Hide Post
Sounds like a job for McGyver!
Just search for mcgyver on this forum.


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Virtuoso
posted Hide Post
If I understand you correctly, when fieldB = 1 you want 3 records; when 2, you want 2 records; otherwise 1 record.
True?
If yes, then use McGuyver.


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Peechy, try this:
  
-* File peechy01.fex
JOIN BLANK WITH SEATS IN CAR TO BLANK IN FSEQ AS MCG
DEFINE FILE CAR
BLANK/A1 WITH SEATS = ' ';
DEFBOD/A6 = IF SEATS EQ 2 AND COUNTER EQ 1 THEN 'URGENT' ELSE 
            IF SEATS EQ 2 AND COUNTER EQ 2 THEN 'MEDIUM' ELSE
			IF SEATS EQ 2 AND COUNTER EQ 3 THEN 'LOW'    ELSE
			IF SEATS EQ 4 AND COUNTER EQ 1 THEN 'URGENT' ELSE
			IF SEATS EQ 4 AND COUNTER EQ 2 THEN 'MEDIUM' ELSE 
			IF SEATS EQ 4 AND COUNTER EQ 3 THEN 'NONE'   ELSE
			IF SEATS EQ 5 AND COUNTER EQ 1 THEN ' '      ELSE 'NONE';
END
TABLE FILE CAR
PRINT DEFBOD
BY CAR
BY MODEL
BY BODYTYPE 
BY SEATS
IF COUNTER LE 3
IF DEFBOD NE NONE
END


The FSEQ file.
MASTER
  
FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.DAT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$


DATA
  
 SOMECHARACTERSFORTHEFSEQFILENOTICETHEFIRSTCHARACTERISASPACE


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
I don't know if this is possible with your data set...?

TABLE FILE CAR
	BY HIGHEST 4 COUNTRY
ON TABLE HOLD AS X
END

TABLE FILE X
	PRINT COUNTRY
ON TABLE HOLD AS Y
MORE
FILE X
WHERE COUNTRY EQ 'W GERMANY' OR 'JAPAN'
MORE
FILE X
WHERE COUNTRY EQ 'W GERMANY'
END

TABLE FILE Y
	PRINT COMPUTE COUNTER/I1 = IF LAST FST.COUNTRY EQ FST.COUNTRY THEN COUNTER + 1 ELSE 1;
	BY COUNTRY
END



McGyver™

G'luck


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Member
posted Hide Post
I will give these suggestions a try and see if it works for what I am doing. Thank you.


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
Member
posted Hide Post
One quick follow up. Where do I put the data element of this? I know where to create the procedure and how to save a master file. Where does the data section that is referenced by Danny-SRL go?


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
Virtuoso
posted Hide Post
I would place the mcguyver in baseapp so you can use it every time




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, 2006Report This Post
Virtuoso
posted Hide Post
Very true!


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
I generally use this variation of the master:
 
 $ThisStructureIsUsedToImplementTheMcGyverTechniqueAndShouldNotBeAltered888888881111111111111111111111111111111111111111111111111111111111111111111111
FILENAME =MACG,SUFFIX =FIX, DATASET=baseapp/macg.mas,$
SEGNAME  =ROOT   ,SEGTYPE=S0
FIELDNAME=BLANK  ,           , FORMAT=A1,ACTUAL=A1,$
SEGNAME  =BABY   ,SEGTYPE=S0 , OCCURS=149
FIELDNAME=DUMMY  ,           , FORMAT=A1,ACTUAL=A1,$
FIELDNAME=COUNTER,ALIAS=ORDER, FORMAT=I4,ACTUAL=I4,$


Save it as macg.mas in baseapp.
It would work the same way "programmatically" i.e. instead of FSEQ just use MACG.
But it is a "cheat" in the sense that the master file IS the data file, and therefore one only needs one file.
The trick is that only the first row may have a blank character in the first column.


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Member
posted Hide Post
So I tried a couple of the suggestions above and was unable to have any records returned. I did try a different technique posted and it seemed to work. Not really sure what I was doing wrong the first time around. Here is what my end code ended up being.

-*******************************************************************************************
-*DECODE FILE
-*******************************************************************************************
DEFINE FILE T_SQL_SERVICE
IND/I10 WITH DESCR = IND + 1;
BLANK/A1 WITH DESCR = '';
END
-RUN

TABLE FILE T_SQL_SERVICE
PRINT DESCR
IND
BLANK
ON TABLE HOLD AS TMP_FILE FORMAT FOCUS
END
-RUN
EX -LINES 4 EDAPUT MASTER,blank_mas,CV,FILE
FILENAME=BLANK_MAS, SUFFIX=FIX,$
SEGNAME=BLANK_MAS, $
FIELD=FIELD1 ,ALIAS= ,A1 ,A1 ,$

FILEDEF BLANK_MAS DISK blank_mas.mas (RECFM F LRECL 1

DEFINE FILE BLANK_MAS
CNT/I3 WITH FIELD1 = CNT + 1;
BLANK/A1 WITH FIELD1 = '';
END
-RUN

-* 8 - ORDER OF DUPLICATION
TABLE FILE BLANK_MAS
PRINT
FIELD1 NOPRINT
BLANK
WHERE CNT LE 10
BY BLANK NOPRINT
ON TABLE HOLD AS TMP_MAS1 FORMAT FOCUS INDEX BLANK
END
-RUN

JOIN BLANK IN TMP_FILE TAG T1 TO MULTIPLE BLANK IN TMP_MAS1 TAG T2 AS JJJ

DEFINE FILE TMP_FILE
DSERV/A40 = IF DESCR EQ 'Alarm' AND T2.FOCLIST EQ 1 THEN 'Burglar Alarm' ELSE
IF DESCR EQ 'Alarm' AND T2.FOCLIST EQ 2 THEN 'Fire Alarm' ELSE
IF DESCR EQ 'Alarm' AND T2.FOCLIST EQ 3 THEN 'Fire Alarm, C-UL' ELSE
IF DESCR EQ 'Alarm' AND T2.FOCLIST GT 3 THEN '' ELSE
IF DESCR EQ 'Building Structure/Exterior Walls' AND T2.FOCLIST EQ '1' THEN 'Exterior Walls' ELSE
IF DESCR EQ 'Building Structure/Exterior Walls' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Curbs/Sidewalks' AND T2.FOCLIST EQ 1 THEN 'Curb' ELSE
IF DESCR EQ 'Curbs/Sidewalks' AND T2.FOCLIST EQ 2 THEN 'Sidewalk' ELSE
IF DESCR EQ 'Curbs/Sidewalks' AND T2.FOCLIST GT 2 THEN '' ELSE
IF DESCR EQ 'Detention Pond/Catch Basin' AND T2.FOCLIST EQ 1 THEN 'Catch Basin' ELSE
IF DESCR EQ 'Detention Pond/Catch Basin' AND T2.FOCLIST EQ 2 THEN 'Detention Basin' ELSE
IF DESCR EQ 'Detention Pond/Catch Basin' AND T2.FOCLIST GT 2 THEN '' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 1 THEN 'Automatic Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 2 THEN 'Cart Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 3 THEN 'Emergency Exit Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 4 THEN 'Entrance Exit' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 5 THEN 'Mall Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 6 THEN 'Manual Entrance Door' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST EQ 7 THEN 'Team Member Entrance' ELSE
IF DESCR EQ 'Entry Door' AND T2.FOCLIST GT 7 THEN '' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 1 THEN 'Air Handler' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 2 THEN 'Chillers' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 3 THEN 'Cooling tower' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 4 THEN 'Exhaust Fan' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 5 THEN 'Forced Air Furnace' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 6 THEN 'HVAC' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 7 THEN 'HVAC Controls' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST EQ 8 THEN 'HVAC Equipment' ELSE
IF DESCR EQ 'HVAC' AND T2.FOCLIST GT 8 THEN '' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 1 THEN 'Beds - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 2 THEN 'Beds - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 3 THEN 'Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 4 THEN 'Grass - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 5 THEN 'Grass - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 6 THEN 'Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 7 THEN 'Plants - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 8 THEN 'Plants - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 9 THEN 'Trees - Building Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST EQ 10 THEN 'Trees - Parking Lot Landscape' ELSE
IF DESCR EQ 'Landscaping' AND T2.FOCLIST GT 10 THEN '' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 1 THEN 'Crosswalk' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 2 THEN 'Fence' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 3 THEN 'Fire Hydrant' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 4 THEN 'Parking Lot' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 5 THEN 'Parking Lot Drive' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 6 THEN 'Parking Lot Landscape' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 7 THEN 'Parking Stalls' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST EQ 8 THEN 'Retaining Wall' ELSE
IF DESCR EQ 'Parking Lot/Drive Aisle' AND T2.FOCLIST GT 8 THEN '' ELSE
IF DESCR EQ 'Roof' AND T2.FOCLIST EQ 1 THEN 'Roof System' ELSE
IF DESCR EQ 'Roof' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Signage - Building' AND T2.FOCLIST EQ 1 THEN 'Building Sign' ELSE
IF DESCR EQ 'Signage - Building' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Signage - Directional' AND T2.FOCLIST EQ 1 THEN 'Directional Sign' ELSE
IF DESCR EQ 'Signage - Directional' AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR IN ('Signage - Pylon','Signage - Pylon and/or Monument') AND T2.FOCLIST EQ 1 THEN 'Pylon or Monument Sign' ELSE
IF DESCR IN ('Signage - Pylon','Signage - Pylon and/or Monument') AND T2.FOCLIST GT 1 THEN '' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 1 THEN 'Landscape Lighting' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 2 THEN 'Lighting Motion Sensor' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 3 THEN 'Natural Gas Pipe' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 4 THEN 'On site Sewer system' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 5 THEN 'Parking Lot Lighting' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST EQ 6 THEN 'Perimeter/Security Lighting' ELSE
IF DESCR EQ 'Utilities - Exterior' AND T2.FOCLIST GT 6 THEN '' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST EQ 1 THEN 'Backflow prevention' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST EQ 2 THEN 'Boilers' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST EQ 3 THEN 'Electrical Panels' ELSE
IF DESCR EQ 'Utilities - Interior' AND T2.FOCLIST GT 3 THEN '' ELSE
DESCR;
END
TABLE FILE TMP_FILE
SUM
DESCR
DSERV
BY DESCR NOPRINT
BY DSERV NOPRINT
WHERE NOT DSERV EQ ''
ON TABLE HOLD AS LISTING FORMAT FOCUS INDEX DESCR
END
-RUN
JOIN
ID IN T_SQL_PROPERTY TO
ID IN T_SQL_SERVICES AS J02
END
JOIN
T_SQL_SERVICES.SERVICE IN T_SQL_PROPERTY TO
SERVICE IN T_SQL_SERVICE AS J03
END
JOIN
T_SQL_SERVICES.REPAIR IN T_SQL_PROPERTY TO
TENANTTYP IN T_SQL_TENANTTY TAG J05 AS J05
END
JOIN
LEFT_OUTER
T_SQL_SERVICE.DESCR IN T_SQL_PROPERTY TO MULTIPLE
DESCR IN LISTING AS J04
END
TABLE FILE T_SQL_PROPERTY
SUM
DSERV AS 'Service'
T_SQL_SERVICE.DESCR
MAX.J05.DESCR AS 'Repairs'
BY PROPID NOPRINT
BY BLDGID NOPRINT
BY ID NOPRINT
BY T_SQL_SERVICE.DESCR NOPRINT
BY DSERV NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END

This seems to give me the result I am looking for. Not sure if there was a more efficient way of getting the same result. Thanks for your help.


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Create multiple rows from single value

Copyright © 1996-2020 Information Builders