Focal Point
[SOLVED] How to culculate the rank value in DataMicgrator?

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7607095926

March 18, 2013, 11:59 PM
jay sun
[SOLVED] How to culculate the rank value in DataMicgrator?
I want transform the data to target table, and add a culculate column, the column value is the rank depent other 2 column.For example:
Source data:
US Product1 100
US Product2 200
UK Product1 300
UK Product2 400

The result data:

US Product1 100 1
US Product2 200 2
UK Product1 300 1
UK Product2 400 2

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 8001
Windows7
Output: PDF, Excel, COMT
March 19, 2013, 07:23 AM
Alex
Sort by country/bt product by value and create a Counter with a COMPUTE using "LAST" to reset the counter.
TABLE FILE CAR
SUM
SALES
COMPUTE COUNTER/I1 = IF COUNTRY EQ LAST COUNTRY THEN COUNTER +1 ELSE 1;
BY  COUNTRY
BY CAR
END

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


WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
March 26, 2013, 05:11 AM
jay sun
Thanks, Alex
Can I get more meaningful result about the "RANK":

This is the default result:


------------------------------------------------------
COUNTRY-------CAR------------SALES------COUNTER
ENGLAND-------JAGUAR-------12000------1
-------------------JENSEN--------0-----------2
-------------------TRIUMPH------0-----------3
FRANCE---------PEUGEOT------0-----------1
ITALY------------ALFA-ROMEO-30200------1
-------------------MASERATI-----0------------2
JAPAN------------DATSUN------43000------1
-------------------TOYOTA-------35030------2
W-GERMANY---AUDI-----------800---------1
-------------------BMW-----------80390-----2
------------------------------------------------------

I-want-the-result-like:
------------------------------------------------------
COUNTRY-------CAR------------SALES------COUNTER
ENGLAND-------JAGUAR-------12000------1
-------------------JENSEN--------0-----------2
-------------------TRIUMPH------0-----------2
FRANCE---------PEUGEOT------0-----------1
ITALY------------ALFA-ROMEO-30200------1
-------------------MASERATI-----0------------2
JAPAN------------DATSUN------43000------1
-------------------TOYOTA-------35030------2
W-GERMANY---BMW-----------80390---------1
-------------------AUDI-----------7800-----2
------------------------------------------------------

This message has been edited. Last edited by: jay sun,


WebFOCUS 8001
Windows7
Output: PDF, Excel, COMT
March 27, 2013, 04:24 AM
Alan B
There is an inbuilt solution:
TABLE FILE CAR
SUM         SALES
BY               COUNTRY
RANKED 
BY HIGHEST TOTAL SALES   NOPRINT
BY               CAR
END



Alan.
WF 7.705/8.007
March 28, 2013, 04:37 AM
jay sun
It's great, Thanks.


WebFOCUS 8001
Windows7
Output: PDF, Excel, COMT