[SOLVED]Sort calculated count field in descending order and limit # of rows displayed

July 20, 2016, 02:47 PM

Hi,
I am new to WebFocus App Studio and had the below question:
I am trying to calculate counts of shipment_id based on courier service names. Below is the code and output:

TABLE FILE WF_RETAIL
SUM
CNT.DST.WF_RETAIL.WF_RETAIL_SHIPMENTS.ID_SHIPFACT AS 'SHIPMENT_COUNT'
BY WF_RETAIL.WF_RETAIL_SHIPPING_OPTIONS.COURIER
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 = endeflt,
$
ENDSTYLE
END

Shipping Company SHIPMENT_COUNT
DL Express 112
Express Star 3446
Logistics Star 467
Parcel Service United 4522
Postal Service 1453

However, in my final output I want to display only the top three rows with the highest numbers of shipments. How do I sort the SHIPMENT_COUNT field in descending order and limit it to three rows only?
(I want my final output to look like this:
Parcel Service United 4522
Express Star 3446
Postal Service 1453)

Thanks,
SSurana

This message has been edited. Last edited by: <Emily McAllister>,

July 20, 2016, 03:19 PM

MartinY

Could be one way

TABLE FILE CAR
RANKED BY HIGHEST RETAIL_COST
BY MODEL
ON TABLE HOLD AS TMP FORMAT FOCUS
END
-RUN

TABLE FILE TMP
PRINT RETAIL_COST
BY HIGHEST RETAIL_COST NOPRINT
BY MODEL
WHERE RANK LE 3;
END
-RUN

July 20, 2016, 03:36 PM

SSurana

Thank you for such prompt responses, Martin & Dan.

Dan, I tried what you suggested and it does sort the shipments in descending order and limits it to three rows. However, it also add two more columns(RANK and SHIPMENT_COUNT - as the first and second columns) to the report output:

RANK SHIPMENT_COUNT Shipping Company SHIPMENT_COUNT
1 4522 Parcel Service United 4522
2 3446 Express Star 3446
3 1453 Postal Service 1453

I could get one of the SHIPMENT_COUNT fields to not print by adding a 'NOPRINT'(as shown below) but could not do so for the 'RANK' column? Is there any way that I can set it to invisible as well?

RANKED BY TOTAL HIGHEST 3 CNT.DST.WF_RETAIL.WF_RETAIL_SHIPMENTS.ID_SHIPFACT NOPRINT AS 'SHIPMENT_COUNT'

Thanks,
Shweta

July 21, 2016, 01:57 PM

krhapner

Does this work for you?

TABLE FILE CAR
SUM CNT.DST.MODEL AS '# Models'
BY HIGHEST 3 TOTAL CNT.DST.MODEL NOPRINT
BY CAR AS 'Car'
END

---------------------------------------
If you start with this:
TABLE FILE CAR
SUM CNT.DST.MODEL
BY CAR
END

Result is:

CAR #Models
ALFA ROMEO 3
AUDI 1
BMW 6
DATSUN 1
JAGUAR 2
JENSEN 1
MASERATI 1
PEUGEOT 1
TOYOTA 1
TRIUMPH 1

Then with the code above...

Result is:
Car # Models
BMW 6
ALFA ROMEO 3
JAGUAR 2

July 21, 2016, 02:28 PM

SSurana

quote:
Originally posted by krhapner:
Does this work for you?

TABLE FILE CAR
SUM CNT.DST.MODEL AS '# Models'
BY HIGHEST 3 TOTAL CNT.DST.MODEL NOPRINT
BY CAR AS 'Car'
END

Then with the code above...

Result is:
Car # Models
BMW 6
ALFA ROMEO 3
JAGUAR 2

Hi krhapner,
Thank you for your response. I took your suggestion and it works, without adding a rank field. However, in my specific code, it sorts the data in ascending order and I don't understand why!
Earlier, I had posted the sample code (on which the sort works fine),but below is my actual code, the results of which gets sorted in ascending order. Any idea why it's happening?

TABLE FILE DQ_DATA_MODEL
SUM
CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO AS 'TOTAL_FAILED_CLAIMS'
BY TOTAL HIGHEST 15 CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO NOPRINT AS 'TOTAL_FAILED_CLAIMS'
BY DQ_DATA_MODEL.DIM_DQ_PROCESS.DQ_PROCESS_DESC NOPRINT
BY DQ_DATA_MODEL.DIM_CLM_SOURCE.DESCRIPTION NOPRINT
BY DQ_DATA_MODEL.DIM_DQ_RULE.DQ_RULE_LOGICAL_FIELD_NAME NOPRINT
BY DQ_DATA_MODEL.DIM_DQ_RULE.DQ_RULE_DESC NOPRINT
BY DQ_DATA_MODEL.DIM_DATE.CAL_MTH NOPRINT
BY DQ_DATA_MODEL.DIM_PROVIDER_NEW.PROV_ID
BY DQ_DATA_MODEL.DIM_PROVIDER_NEW.PROV_NAME
ON TABLE SUBHEAD
"FAILED CLAIMS BY PROVIDER"
HEADING
"PROCESS NAME: &DQ_PROCESS_DESC"
"FIELD NAME: &DQ_RULE_LOGICAL_FIELD_NAME"
"RULE DESCRIPTION: &DQ_RULE_DESC "
"MONTH: &CAL_MTH, YEAR: 2015"
"CLAIM SOURCE: &DESCRIPTION"
WHERE ( DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.DQ_SCORE EQ 0 ) AND ( DQ_DATA_MODEL.DIM_DQ_PROCESS.DQ_PROCESS_DESC EQ '&DQ_PROCESS_DESC.(FIND DQ_DATA_MODEL.DIM_DQ_PROCESS.DQ_PROCESS_DESC IN DQ_DATA_MODEL).DQ_PROCESS_DESC:.' ) AND ( DQ_DATA_MODEL.DIM_DQ_RULE.DQ_RULE_LOGICAL_FIELD_NAME EQ '&DQ_RULE_LOGICAL_FIELD_NAME.(FIND DQ_DATA_MODEL.DIM_DQ_RULE.DQ_RULE_LOGICAL_FIELD_NAME IN DQ_DATA_MODEL).DQ_RULE_LOGICAL_FIELD_NAME:.' ) AND ( DQ_DATA_MODEL.DIM_DATE.CAL_MTH EQ '&CAL_MTH.(FIND DQ_DATA_MODEL.DIM_DATE.CAL_MTH IN DQ_DATA_MODEL).CAL_MTH:.' ) AND ( DQ_DATA_MODEL.DIM_CLM_SOURCE.DESCRIPTION EQ '&DESCRIPTION.(FIND DQ_DATA_MODEL.DIM_CLM_SOURCE.DESCRIPTION IN DQ_DATA_MODEL).DESCRIPTION:.' ) AND ( DQ_DATA_MODEL.DIM_DQ_RULE.DQ_RULE_DESC EQ '&DQ_RULE_DESC.(FIND DQ_DATA_MODEL.DIM_DQ_RULE.DQ_RULE_DESC IN DQ_DATA_MODEL).DQ_RULE_DESC:.' );
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:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=DATA,
COLUMN=N8,
FOCEXEC=IBFS:/WFC/Repository/DQ_Rules/~admin/Demo_Detail.fex( \
DQ_PROCESS_DESC=N3 \
DESCRIPTION=N4 \
DQ_RULE_LOGICAL_FIELD_NAME=N5 \
DQ_RULE_DESC=N6 \
CAL_MTH=N7 \
PROV_ID=N8 \
),
TARGET='_self',
$
TYPE=DATA,
COLUMN=N5,
FOCEXEC=IBFS:/WFC/Repository/DQ_Rules/~admin/Demo_Detail.fex( \
PROV_ID=N5 \
DQ_PROCESS_DESC=N1 \
DESCRIPTION=N2 \
DQ_RULE_LOGICAL_FIELD_NAME=N8 \
DQ_RULE_DESC=N3 \
CAL_MTH=N4 \
),
TARGET='_self',
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
JUSTIFY=LEFT,
$
ENDSTYLE
END

Thanks,
Shweta

July 21, 2016, 02:47 PM

krhapner

I am really not sure

The one thing I thought of would be to change your

quote:
BY TOTAL HIGHEST 15

quote:
BY HIGHEST 15 TOTAL

Another idea would be to change HIGHEST to TOP.

No idea if that will work or not. Sorry!

July 21, 2016, 02:59 PM

SSurana

I tried doing that as well, but it did not work. Thanks for replying though.
The only difference between this code and the sample code (using shipments) which I had posted earlier, is that I included few more fields which are set to 'noprint' and are used as parameters to be passed to another drill-down report. However, I am not sure how any why that would mess up the sorting order.
Let me know if you have any other suggestions.

Thanks,
Shweta

July 21, 2016, 03:17 PM

krhapner

Hmm.. that is strange. It shouldn't make a difference. Could you try running this piece by piece? What I mean is, could you see if you can get the correct results without the extra by statements?

quote:
TABLE FILE DQ_DATA_MODEL
SUM
CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO AS 'TOTAL_FAILED_CLAIMS'
BY TOTAL HIGHEST 15 CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO NOPRINT AS 'TOTAL_FAILED_CLAIMS'
END

July 21, 2016, 03:19 PM

Dan Satchell

Try this:

TABLE FILE WF_RETAIL
 SUM
  WF_RETAIL.WF_RETAIL_SHIPPING_OPTIONS.COURIER
  CNT.DST.WF_RETAIL.WF_RETAIL_SHIPMENTS.ID_SHIPFACT                    AS 'SHIPMENT_COUNT'
 BY TOTAL HIGHEST 3 CNT.DST.WF_RETAIL.WF_RETAIL_SHIPMENTS.ID_SHIPFACT  NOPRINT
 BY WF_RETAIL.WF_RETAIL_SHIPPING_OPTIONS.COURIER                       NOPRINT
  .
  .
  .

July 22, 2016, 09:47 AM

SSurana

quote:
Originally posted by krhapner:
Hmm.. that is strange. It shouldn't make a difference. Could you try running this piece by piece? What I mean is, could you see if you can get the correct results without the extra by statements?

quote:
TABLE FILE DQ_DATA_MODEL
SUM
CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO AS 'TOTAL_FAILED_CLAIMS'
BY TOTAL HIGHEST 15 CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO NOPRINT AS 'TOTAL_FAILED_CLAIMS'
END

Hi,
I tried doing it. The above code only returned one grand total as there was no other field included in the group by statement. So, I added just the Prov_ID, based on which I want to get the counts and removed all the extra by statements. It still sorts in ascending order.

TABLE FILE DQ_DATA_MODEL
-*WHERE DQ_DATA_MODEL.DIM_DQ_PROCESS.DQ_PROCESS_DESC EQ 'MA ENCOUNTER' AND DQ_DATA_MODEL.DIM_DATE.CAL_MTH EQ '8' AND DQ_DATA_MODEL.DIM_CLM_SOURCE.DESCRIPTION EQ 'BEACON';
SUM
CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO AS 'TOTAL'
BY TOTAL HIGHEST 15 CNT.DST.DQ_DATA_MODEL.FACT_CLAIM_DATA_QUALITY_DETAIL.CLM_NO NOPRINT AS 'TOTAL'
BY DQ_DATA_MODEL.DIM_PROVIDER_NEW.PROV_ID

Thanks,
Shweta