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.
England || Jaguar Japan || Toyota Italy || Maserati France || Peugeot
into:
England || Japan || Italy || France Jaguar || Toyota || Maserati || Peugeot
I try Pivot topic, but no luck(maybe I'm not using Pivot correctly), any other way to transpose this table?This message has been edited. Last edited by: nox,
TABLE FILE CAR
SUM
CAR.COMP.CAR
ACROSS LOWEST CAR.ORIGIN.COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,
$
ENDSTYLE
END
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005
Nice code! But I have realize that I did not mansion about second part of my objective.
My next objective is able to transpose the "CAR" table result into a DB not on report. and another condition is that each column's rows may be increment through time(meaning there will be more country add into "Country" column and more car into "CAR" column as well)
I am confused by this requirement. As you saw by my example that the data can be displayed how you want in a report after it is loaded in the table by using the ACROSS. I don't understand why the data needs to be loaded into the db the way you describe. The rows of data can be country and car and then you can insert additional rows easily.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005
I know it is sound confused in logical view, but what actually happen is due to some older DB system that contain some tables wish to transpose and load into new DB. Since there is some bad designing in older DB system.
Assume in old DB's table look like this:
Infro. || Data1 || Data2 ------------------------------ ID || ABC1234 || ABC456 FName || Mike || Anna LName || Kennedy || Snow Sex || Male || Female
You can store anything in a three column database 07/03/08 | by Clif [mail] | Categories: DataMigrator, SQL I was cleaning up my desk before the holiday and found something I'd written a while back in response to a prospect's request. I had thought about proposing an enhancement to DataMigrator to automate this process, but it died due to lack of interest. However, I think others may find it interesting so I'm posting it here.
How to pivot a table containing name and value pairs While most input data is represented in a columnar structure, it's also possible to represent data with name and value pairs. This is common for XML structures but is also used for flat files.
It could also be used with a relational database. The same three column table could be used to store everything. But that would be madness, right? (Actually it would be called NoSQL) When input data is represented this way, it's necessary to "pivot" the data to load it into a normal relational table.
For example consider the following table:
1Last Name ADAMKIEWICZ 1First Name GREGORY 1Position Line Worker 2Last Name ADAMS 2First Name GAY 2Position Line Worker 3Last Name ALBOR 3First Name STEVEN 3Position Technician 4Last Name ALSMAN 4First Name RANDY 4Position Line Worker 5Last Name ARNOLD 5First Name HAROLD 5Position Technician 6Last Name AUSTIN 6First Name BARRY 6Position Technician 7Last Name BALMER 7First Name PETER 7Position Technician 8Last Name BALNAVE 8First Name WILLIAM 8Position Line Manager The first column of each table is an ID number, the second is a Name such as "Last Name" or "First Name" and the third column is the value. This file could be described by the following synonym: FILENAME=hrflat, SUFFIX=FIX , DATASET=baseapp/hrflat.ftm, $ SEGMENT=HRFLAT, SEGTYPE=S0, $ FIELDNAME=ID_NUM, ALIAS=ID_NUM, USAGE=I2, ACTUAL=A2, $ FIELDNAME=PNAME, ALIAS=PNAME, USAGE=A13, ACTUAL=A13, $ FIELDNAME=PVALUE, ALIAS=PVALUE, USAGE=A12, ACTUAL=A12, $
In order to load the data into a relational database, the table must be pivoted, or flipped, with the result:
ID_NUM Last Name First Name Position 1 ADAMKIEWICZ GREGORY Line Worker 2 ADAMS GAY Line Worker 3 ALBOR STEVEN Technician 4 ALSMAN RANDY Line Worker 5 ARNOLD HAROLD Technician 6 AUSTIN BARRY Technician 7 BALMER PETER Technician 8 BALNAVE WILLIAM Line Manager As it happens, this type of pivot is trivial to in FOCUS using TABLE and the verb ACROSS: TABLE FILE HRFLAT WRITE PVALUE BY ID_NUM ACROSS PNAME ON TABLE HOLD FORMAT ALPHA ON TABLE SET ASNAMES ON END
The disadvantage of this approach is that it requires writing a TABLE request, there's no way to do this in a DM flow, so using this data would be two-step process, first run the TABLE to create a flat file, then load it into a relational table. With the setting for ASNAME the generated synonymn has column names of PVAFirst Name, PVALast Name, PVAPosition. That may mean editing the generated synonym to enter the desired column names.
It's possible to write a SQL SELECT statement to do the same thing. SELECT ID_NUM , MAX( CASE WHEN PNAME = 'Last Name' THEN PVALUE END AS "Last Name" , MAX( CASE WHEN PNAME = 'First Name' THEN PVALUE END ) AS "First Name" , MAX( CASE WHEN PNAME = 'Position' THEN PVALUE END ) AS "Position" FROM HRFLAT T1 GROUP BY ID_NUM;
This could even be done using the DataMigrator user interface in the Column Selection grid:
The disadvantage of this approach is that you need to know all the possible values for the name column to write the select statement. However once you do that, the synonym that is generated has the correct column names.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005
SELECT ID_NUM , MAX( CASE WHEN PNAME = 'Last Name' THEN PVALUE END AS "Last Name" , MAX( CASE WHEN PNAME = 'First Name' THEN PVALUE END ) AS "First Name" , MAX( CASE WHEN PNAME = 'Position' THEN PVALUE END ) AS "Position" FROM HRFLAT T1 GROUP BY ID_NUM;
I try using this method, but I end up something like this:
ID_NUM || Last Name || First Name || Position 1 || ADAMKIEWICZ || NULL || NULL 2 || NULL || GREGORY || NULL 3 || NULL || NULL || Line Worker 4 || ADAMS || NULL || NULL 5 || NULL || GAY || NULL 6 || NULL || NULL || Line Worker
since GROUP BY ID_NUM then how can I get the result like:
quote:
ID_NUM Last Name First Name Position 1 ADAMKIEWICZ GREGORY Line Worker 2 ADAMS GAY Line Worker 3 ALBOR STEVEN Technician 4 ALSMAN RANDY Line Worker 5 ARNOLD HAROLD Technician 6 AUSTIN BARRY Technician 7 BALMER PETER Technician 8 BALNAVE WILLIAM Line Manager
My bad, I didn't test this out... The first problem is that Focal Point didn't keep your input data aligned and the SQL had a missing right ) I am going to e-mail you updated input file and SQL
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005