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
Maybe like this:
Set DateFormat = 'DD/MM/YYYY';
INPUT:
LOAD * INLINE [
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
];
TMP:
LOAD ID,Activity,
min(Date) as Date,
'Min' as Flag
RESIDENT INPUT
GROUP BY ID, Activity;
LOAD ID, Activity,
Max(Date) as Date,
'Max' as Flag
RESIDENT INPUT
GROUP BY ID, Activity;
LEFT JOIN (INPUT)
LOAD ID, Activity, Date, concat(Flag,'/') as Flag
RESIDENT TMP
GROUP BY ID, Activity, Date;
DROP TABLE TMP;
Please see the attached file. I have used expression in chart.
Hi Swuehl,
I tried this code:
DATASTEMP:
Load
[%Key field],
StepAtividade,
min(Data) as Data,
'Primeiro' as StepAtividadeOrder
Resident DATAS
Group by [%Key field], StepAtividade;
Load
[%Key field],
StepAtividade,
max(Data) as Data,
'Ultimo' as StepAtividadeOrder
Resident DATAS
Group by [%Key field], StepAtividade;
LEFT JOIN (DATAS)
Load
[%Key field],
StepAtividade,
Data,
Concat(StepAtividadeOrder,'/') as StepAtividadeOrder
Resident DATASTEMP
Group by [%Key field], StepAtividade, Data;
Drop table DATASTEMP;
But the result was like this:
It did not make the diference between the Max and Min.
Hey Kush, This worked very great, but one of the motives to ask this in the Script it is to filter in a ListBox. There is any way to add the results (Min, Max, Min/Max, N/A) in a list box?
But my original script worked for you, right?
Could you upload your QVW or a sample file that demonstrates your issue?
(Or a sample of your input data)
Please see the below screenshots where I handled it in the load script:
Here is the data model with data:
And finally, a table box and list box:
Hope this helps.
I corrected somethings in my Script and now it is working. Thans very much!
My problem now it is that i have equal dates for the same ID and Activity, and the result it is like below:
ID | Activity | Date | Rank |
1 | A | 01/01/2015 | Min |
1 | A | 01/01/2015 | Min |
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 need to have only one Min and one Max for each ID+Activity.
I Have another column that i can select who can be the Min, like below:
ID | Activity | Date | Rank | Source |
1 | A | 01/01/2015 | - | Executed |
1 | A | 01/01/2015 | Min | Rework |
1 | A | 03/01/2015 | Max | Rework |
1 | B | 04/01/2015 | Max/Min | Executed |
2 | B | 05/01/2015 | Min | Executed |
2 | B | 06/01/2015 | Max | Rework |
3 | C | 07/01/2015 | Max/Min | Executed |
That worked too! Thans very much!
But same problem of the other solution:
Now i have equal dates for the same ID and Activity, and the result it is like below:
ID | Activity | Date | Rank |
1 | A | 01/01/2015 | Min |
1 | A | 01/01/2015 | Min |
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 need to have only one Min and one Max for each ID+Activity.
I Have another column that i can select who can be the Min, like below:
ID | Activity | Date | Rank | Source |
1 | A | 01/01/2015 | - | Executed |
1 | A | 01/01/2015 | Min | Rework |
1 | A | 03/01/2015 | Max | Rework |
1 | B | 04/01/2015 | Max/Min | Executed |
2 | B | 05/01/2015 | Min | Executed |
2 | B | 06/01/2015 | Max | Rework |
3 | C | 07/01/2015 | Max/Min | Executed |
Sorry, I don't understand. How do you tell which record should be flagged as 'Min' and which not?