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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.
I need to RANK a table on two fields, meaning that if the first one is the same, the data is in addition sorted by the second field. I can achieve that by using
RANKED BY HIGHEST field1
RANKED BY HIGHEST field2
But now I need to store that in a HOLD file and access both RANK-fields in a following calculation (for example:
WHERE RANK1 EQ 1 AND valuefield GT 10000 OR RANK1 EQ 1 AND RANK2 EQ 1
).
I tried the following example:
TABLE FILE BASEAPP/CAR
SUM
SALES
BY COUNTRY
RANKED AS 'RNK_SALES' BY HIGHEST SALES NOPRINT
RANKED AS 'RNK_SEATS' BY HIGHEST SEATS
BY CAR
END
which gives me the output with both named RANK-columns. But the following doesn't work:
TABLE FILE BASEAPP/CAR
SUM
SALES
BY COUNTRY
RANKED AS 'RNK_SALES' BY HIGHEST SALES NOPRINT
RANKED AS 'RNK_SEATS' BY HIGHEST SEATS
BY CAR
ON TABLE HOLD AS TEMP_FILE
END
TABLE FILE TEMP_FILE
PRINT
*
WHERE RNK_SALES EQ 1 OR RNK_SEATS EQ 1
END
since it doesn't recognise RNK_SALES as a field. Using
?FF TEMP_FILE
I learned that WF does save the columns in the HOLD file only as "RANK" not by their names.
Is there any trick I can do there? I can use them by their column notation E0x and E0y but I consider that not a good solution as it is not comprehensible.
Cheers, JohnnyThis message has been edited. Last edited by: FP Mod Chuck,
Try to put "SET ASNAME=ON" on the top so you can refer the "RNK_SALES" and "RNK_SEATS" in the TEMP_FILE. Here are the meaning of the SET parameters.This message has been edited. Last edited by: Pku,