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

Check which value is the Min and Max in Load Script

Fri, 19 Jun 2015 17:32:01 GMT

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

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

swuehl — Fri, 19 Jun 2015 17:39:52 GMT

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

Kushal_Chawda — Fri, 19 Jun 2015 18:15:17 GMT

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

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

Fri, 19 Jun 2015 18:45:14 GMT

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.

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

Fri, 19 Jun 2015 18:57:30 GMT

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?

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

swuehl — Fri, 19 Jun 2015 18:59:36 GMT

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)

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

sinanozdemir — Fri, 19 Jun 2015 19:22:34 GMT

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.

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

Fri, 19 Jun 2015 22:04:19 GMT

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

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

Fri, 19 Jun 2015 22:05:18 GMT

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

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

swuehl — Fri, 19 Jun 2015 22:10:29 GMT

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

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

Fri, 19 Jun 2015 22:14:12 GMT

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

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

swuehl — Fri, 19 Jun 2015 22:49:59 GMT

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;

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

MarcoWedel — Fri, 19 Jun 2015 23:22:12 GMT

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

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

Kushal_Chawda — Sat, 20 Jun 2015 06:01:33 GMT

Hi,

Please see the attached.