Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Check which value is the Min and Max in Load Script

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:

IDActivityDate
1A01/01/2015
1A02/01/2015
1A03/01/2015
1B04/01/2015
2B05/01/2015
2B06/01/2015
3C07/01/2015

The result could be something like this:

 

IDActivityDateRank
1A01/01/2015Min
1A02/01/2015
1A03/01/2015Max
1B04/01/2015Max/Min
2B05/01/2015Min
2B06/01/2015Max
3C07/01/2015Max/Min

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

BR//Marcelo Fonseca

13 Replies
swuehl
MVP
MVP

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;

Kushal_Chawda

Please see the attached file. I have used expression in chart.

Not applicable
Author

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:

1.PNG

It did not make the diference between  the Max and Min.

Not applicable
Author

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?

swuehl
MVP
MVP

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)

sinanozdemir
Specialist III
Specialist III

Please see the below screenshots where I handled it in the load script:

Capture.PNG

Here is the data model with data:

Capture2.PNG

And finally, a table box and list box:

Capture3.PNG

Hope this helps.

Not applicable
Author

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:

   

IDActivityDateRank
1A01/01/2015Min
1A01/01/2015Min
1A03/01/2015Max
1B04/01/2015Max/Min
2B05/01/2015Min
2B06/01/2015Max
3C07/01/2015Max/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:

    

IDActivityDateRankSource
1A01/01/2015-Executed
1A01/01/2015MinRework
1A03/01/2015MaxRework
1B04/01/2015Max/MinExecuted
2B05/01/2015MinExecuted
2B06/01/2015MaxRework
3C07/01/2015Max/MinExecuted
Not applicable
Author

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:

   

IDActivityDateRank
1A01/01/2015Min
1A01/01/2015Min
1A03/01/2015Max
1B04/01/2015Max/Min
2B05/01/2015Min
2B06/01/2015Max
3C07/01/2015Max/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:

IDActivityDateRankSource
1A01/01/2015-Executed
1A01/01/2015MinRework
1A03/01/2015MaxRework
1B04/01/2015Max/MinExecuted
2B05/01/2015MinExecuted
2B06/01/2015MaxRework
3C07/01/2015Max/MinExecuted
swuehl
MVP
MVP

Sorry, I don't understand. How do you tell which record should be flagged as 'Min' and which not?