Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Try this:
Tab:
LOAD
CustomerID,
Min(ApptRank) AS ApptRank
FROM
...
Group By CustomerID ;
Left Join(Tab)
LOAD *
FROM
...;