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] Error while displaying data by joining multiple tables (OLAP)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Error while displaying data by joining multiple tables (OLAP)
 Login/Join
 
Gold member
posted
Hi,

I have a data file and two dimension files. My requirement is to join the data file with the two dimension files, and then show the report such that in the BY clause I will have three fields of the 1st Dimension, and in the ACROSS I would have 1 fieldsof the 2nd dimension.

Unfortunately, when I try this, I am getting the following error message:

(FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH

Can anyone please assist?

The code is as below:

DIMENSION FILE 1:

FILENAME=LOCHIER, SUFFIX=SQLORA ,$
SEGNAME=LOCHIER, SEGTYPE=S1 ,$
FIELD=LOCATION_CD, ALIAS=LOCATION_CD, FORMAT=A6, INDEX=I, TITLE='Location, Cd', $
FIELD=LOCATION_CATEGORY, ALIAS=LOCATION_CATEGORY, FORMAT=A11, INDEX=I, TITLE='Location,Category', WITHIN='*Location Hierarchy', DESC='Location,Category', $
FIELD=LOCATION_GROUP, ALIAS=LOCATION_GROUP, FORMAT=A20, INDEX=I, TITLE='Location,Group', WITHIN='LOCATION_CATEGORY', DESC='Location,Group', $
FIELD=AREA, ALIAS=AREA_NAME, FORMAT=A32, INDEX=I, TITLE='Area', WITHIN='LOCATION_GROUP', DESC='Area',$
FIELD=REGION, ALIAS=REGION_NAME, FORMAT=A32, INDEX=I, TITLE='Region', WITHIN='AREA', DESC='Region',$
FIELD=DISTRICT, ALIAS=DISTRICT_NAME, FORMAT=A32, INDEX=I, TITLE='District', WITHIN='REGION', DESC='District',$
FIELD=EQSP, ALIAS=EQSP_NAME, FORMAT=A44, INDEX=I, TITLE='EQSP', WITHIN='DISTRICT', DESC='EQSP',$
FIELD=CITY, ALIAS=CITY_NAME, FORMAT=A39, INDEX=I, TITLE='City', WITHIN='EQSP', DESC='City', $



Dimesion File 2:

FILENAME=EQTDIM, SUFFIX=SQLORA ,$
SEGNAME=EQTDIM, SEGTYPE=S1 ,$
FIELD=EQUIPMENT_TYPE_SIZE_HT, ALIAS=EQUIPMENT_TYPE_SIZE_HT, FORMAT=A8, INDEX=I, TITLE='Eqt Type Size Ht', WITHIN='EQUIPMENT_TYPE_SIZE_SUBGROUP', DESC='Eqt Type Size Ht', $
FIELD=EQUIPMENT_TYPE_SIZE_SUBGROUP, ALIAS=EQUIPMENT_TYPE_SIZE_SUBGROUP, FORMAT=A9, INDEX=I, TITLE='Eqt Type Size Grp', WITHIN='EQUIPMENT_TYPE_SIZE_GROUP', DESC='Eqt Type Size Grp', $
FIELD=EQUIPMENT_TYPE_SIZE_GROUP, ALIAS=EQUIPMENT_TYPE_SIZE_GROUP, FORMAT=A6, INDEX=I, TITLE='Eqt Type Size SubGrp', WITHIN='EQUIPMENT_SUBCATEGORY', DESC='Eqt Type Size SubGrp', $
FIELD=EQUIPMENT_SUBCATEGORY, ALIAS=EQUIPMENT_SUBCATEGORY, FORMAT=A13, INDEX=I, TITLE='Eqt Sub-Category', WITHIN='EQUIPMENT_CATEGORY', DESC='Eqt Sub-Category', $
FIELD=EQUIPMENT_CATEGORY, ALIAS=EQUIPMENT_CATEGORY, FORMAT=A22, INDEX=I, TITLE='Eqt Category', WITHIN='*Equipment Dimension', DESC='Eqt Category', $


Data File:

FILENAME=EQTPERF, SUFFIX=SQLORA ,$
SEGNAME=EQTPERF, SEGTYPE=S3 ,$

FIELD=ACCOUNTING_YEAR_WK, ALIAS=ACCOUNTING_YEAR_WK, FORMAT=I6, INDEX=I, TITLE='Acct Year Wk', $
FIELD=EQUIPMENT_TYPE_SIZE_HT, ALIAS=EQUIPMENT_TYPE_SIZE_HT, FORMAT=A8, INDEX=I, TITLE='Eqt Type Size Ht', $
FIELD=LOCATION_CD, ALIAS=LOCATION_CD, FORMAT=A6, INDEX=I, TITLE='Location Cd', $
FIELD=VOLUME_CNT, ALIAS=VOLUME_COUNT_VALUE, FORMAT=I8, TITLE='Eqt Cnt', $



Fex Code:

JOIN
EQTPERF.EQTPERF.EQUIPMENT_TYPE_SIZE_HT IN EQTPERF TO MULTIPLE
EQTDIM.EQTDIM.EQUIPMENT_TYPE_SIZE_HT IN EQTDIM AS J0
END
JOIN
EQTPERF.EQTPERF.LOCATION_CD IN EQTPERF TO MULTIPLE LOCHIER.LOCHIER.LOCATION_CD
IN LOCHIER AS J1
END
TABLE FILE EQTPERF
SUM
VOLUME_CNT AS 'Container Count'
BY AREA
BY REGION
BY DISTRICT
BY ACCOUNTING_YEAR_WK
ACROSS EQUIPMENT_SUBCATEGORY
HEADING
"Performance Report"
FOOTING
"---- Generated on <+0>&DATE<+0> at <+0>&TOD<+0> ----"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET AUTODRILL ON
ON TABLE SET OLAPPANE HIDDEN
.
.
.
.
The rest of it is just Styling, and is irrelevant.

Any idea where I am going wrong?

Thanks,
Ved

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


Web Focus 7.1.6
Hosted on Unix Box
 
Posts: 61 | Registered: August 17, 2007Report This Post
<JG>
posted
 
The problem is that your joins are creating a multipath structure
like this

        *********             
        *       *             
        *********             
            *             
            *             
      *************             
      *           *             
      *           *             
  *********   **********             
  *       *   *        *             
  *********   **********      
  
What you actually need is a single path structure
Like this
  
        *********             
        *       *             
        *********             
            *             
            *  
        *********             
        *       *             
        *********             
            *             
            *                      
        *********             
        *       *             
        *********  
 


Join LOCHIER to EQTPERF to EQTDIM
 
Report This Post
Gold member
posted Hide Post
Got it... I just removed the keyword 'MULTIPLE' from the JION clauses. Now it works like a charm.

I guess it indirectly does the same thing as you have mentioned here.


Web Focus 7.1.6
Hosted on Unix Box
 
Posts: 61 | Registered: August 17, 2007Report 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] Error while displaying data by joining multiple tables (OLAP)

Copyright © 1996-2020 Information Builders