Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
By the column "Source". When this happen, the priority it is where is filled "Rework".
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;
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
Hi,
Please see the attached.