Having the Min Value

[#1]

I have a dataset that can compares 2 tables associated by a common field (CustomerID). The first is CustomerID and CallDate and the other is CustomerID, ApptDate and ApptRank. ApptRank is based on the Appointment Status not on any "Order By".

I can have multiple Appointments per Customer Call but I only want to load in the script the one row per CustomerID, CallDate with the Minimum ApptRank. Below you see my dataset of the 2 joined tables. I want to have a table be just the bottom 2 rows.

Can anyone help? I'm thinking there should be something available in a Preceding Load but I haven't figured it out.

[chiru_thota]

May be helpful for you.have a look at attached QVW.

//Fecth Customer and minimum Rank

Table1:

LOAD CustomerID,

    min(ApptRank) as ApptRank

FROM

(ooxml, embedded labels, table is Sheet1) group by CustomerID ;

//create a composite key Filter to filter out unncesarry data

Table2:

LOAD CustomerID,

    min(ApptRank),

    CustomerID&'-'&ApptRank as Filter

resident Table1 group by CustomerID,CustomerID&'-'&ApptRank;

//Final out put

Final_table:

NoConcatenate

LOAD CustomerID,

    CallDate,

    ApptDate,

    ApptRank

FROM

(ooxml, embedded labels, table is Sheet1) where Exists(Filter,CustomerID&'-'&ApptRank);

drop tables Table1,Table2;

[felipe_dutra]

Try this:

Tab:

LOAD

CustomerID,

Min(ApptRank) AS ApptRank

FROM

...

Group By CustomerID ;

Left Join(Tab)

LOAD *

FROM

...;