Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
MVP
MVP

Re: Check which value is the Min and Max in Load Script

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;

Re: Check which value is the Min and Max in Load Script

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

Not applicable

Re: Check which value is the Min and Max in Load Script

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

Re: Check which value is the Min and Max in Load Script

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?

MVP
MVP

Re: Check which value is the Min and Max in Load Script

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
Valued Contributor III

Re: Check which value is the Min and Max in Load Script

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

Re: Check which value is the Min and Max in Load Script

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

Re: Check which value is the Min and Max in Load Script

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
MVP
MVP

Re: Check which value is the Min and Max in Load Script

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

Community Browser