Check which value is the Min and Max in Load Script

[#1]

Hi,

I would like to attach a field to my data, to determined which line has the max value or min value for the combination of ID+Activity in my Script Load:

ID	Activity	Date
1	A	01/01/2015
1	A	02/01/2015
1	A	03/01/2015
1	B	04/01/2015
2	B	05/01/2015
2	B	06/01/2015
3	C	07/01/2015

The result could be something like this:

ID	Activity	Date	Rank
1	A	01/01/2015	Min
1	A	02/01/2015
1	A	03/01/2015	Max
1	B	04/01/2015	Max/Min
2	B	05/01/2015	Min
2	B	06/01/2015	Max
3	C	07/01/2015	Max/Min

I tried some combination of max and min, but did not get results like this one that i was expecting.

BR//Marcelo Fonseca

[#2]

By the column "Source". When this happen, the priority it is where is filled "Rework".

[swuehl]

Maybe like this:

Set DateFormat = 'DD/MM/YYYY';

INPUT:
LOAD * INLINE [
ID, Activity, Date, Source
1, A, 01/01/2015, Rework
1, A, 01/01/2015, Executed
1, A, 02/01/2015, Rework
1, A, 03/01/2015, Executed
1, B, 04/01/2015, Rework
2, B, 05/01/2015, Rework
2, B, 06/01/2015, Executed
3, C, 07/01/2015, Executed
];


TMP:
LOAD ID,Activity, 
  FirstSortedValue(Source, Date -ord(left(Source,1))/1000) as Source,
    min(Date) as Date,
   'Min' as Flag
RESIDENT INPUT
GROUP BY ID, Activity;

LOAD ID, Activity, 
  FirstSortedValue(Source, -(Date - ord(left(Source,1))/1000)) as Source,
    Max(Date) as Date,
   'Max' as Flag
RESIDENT INPUT
GROUP BY ID, Activity;

LEFT JOIN (INPUT)
LOAD ID, Activity, only(Source) as Source, Date, concat(Flag,'/') as Flag
RESIDENT TMP
GROUP BY ID, Activity, Date;

DROP TABLE TMP;

[MarcoWedel]

Hi,

another one (having the same issue I guess):

table1:
LOAD * FROM [https://community.qlik.com/thread/169200] (html, codepage is 1252, embedded labels, table is @1);

tabRanks:
CrossTable (Rank,Date,2)
LOAD ID,
    Activity,
    FirstSortedValue(Date,Date) as Min,
    FirstSortedValue(Date,-Date) as Max
Resident table1
Group By ID, Activity;

Left Join (table1)
LOAD ID,
    Activity,
    Date,
    Concat(Rank,'/') as Rank
Resident tabRanks
Group By ID, Activity, Date;

DROP Table tabRanks;

hope this helps

regards

Marco

[Kushal_Chawda]

Hi,

Please see the attached.