topic Re: Having the Min Value in QlikView

Having the Min Value

Tue, 18 Mar 2014 01:50:44 GMT

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.

Re: Having the Min Value

chiru_thota — Tue, 18 Mar 2014 02:50:38 GMT

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;

Re: Having the Min Value

felipe_dutra — Tue, 18 Mar 2014 03:00:49 GMT

Try this:

Tab:

LOAD

CustomerID,

Min(ApptRank) AS ApptRank

FROM

...

Group By CustomerID ;

Left Join(Tab)

LOAD *

FROM

...;