Find Sales Rank that is stored in separate table

[ciaran_mcgowan]

Hi,

I have a table that isn't linked to anything else that stores a Salesperson's Rank. There are a lot of values (A - Z) that start at €0 = Z, €100 = Y all the way up to €1,000,000 = A. Once the sum of their sales passes these numbers, that is the rank they will be awarded (e.g. €99 = Z and €100 = Y).

Is there a better way to do this that what I have in mind?

Sales_tmp:

LOAD SalesPersonID,

          Sum(Sales)     AS SalesTotal

Resident

Sales

GROUP BY SalesPersonID;

Rank:

LOAD Rank,

          SalesTarget

FROM Rank;

MatchRank:

IntervalMatch(SalesTotal)

LOAD DISTINCT

          Previous(SalesTarget) AS SalesTarget_MIN,

          SalesTarget

Resident Rank;

I'm not sure if I should try this approach or try something with variables.

[Kushal_Chawda]

what is the expected output? Can you draw output?

[jonathandienst]

The logic looks OK to me - I would also use interval matching to deal with this. What is your concern?

EDIT I would add a join to the interval matching load rather than a straight load. That creates an association between the tables on Sales Target:

MatchRank:

IntervalMatch(SalesTotal)

Left Join LOAD DISTINCT

          Previous(SalesTarget) AS SalesTarget_MIN,

          SalesTarget

Resident Rank;

[jonathandienst · Accepted solution]

I often use an extra step after interval matching to clean up a little, although it is not strictly necessary.

Sales_tmp:

LOAD SalesPersonID,

          Sum(Sales)     AS SalesTotal

Resident

Sales

GROUP BY SalesPersonID;

Rank:

LOAD Rank,

          SalesTarget

FROM Rank;

MatchRank:

IntervalMatch(SalesTotal)

Left Join LOAD DISTINCT

          Previous(SalesTarget) AS SalesTarget_MIN,

          SalesTarget

Resident Rank;

Left Join LOAD DISTINCT

     SalesTarget,

     Rank

Resident Rank;

DROP Table Rank;