topic Re: Find specific time associated with max/min value in QlikView

Find specific time associated with max/min value

Fri, 05 Dec 2014 22:27:10 GMT

Hi,

I have the following table:

Time High Low

...

11 7 1

12 5 2

13 3 1

14 6 3

...

How do you find the time when maximum high and minimum low occurred between, for example, time 11 and 14?

Many thanks

Marcus

Re: Find specific time associated with max/min value

MK_QSL — Fri, 05 Dec 2014 23:16:34 GMT

T1:

LOAD *, High&Low as Key Inline

[

Time, High, Low

11, 7, 1

12, 5, 2

13, 3, 1

14, 6, 3

];

Left Join (T1)

Load Max(High) & Min(Low) as Key2 Resident T1;

T2:

Load Time as RequestedTime Resident T1 Where Key = Key2;

DROP Fields Key, Key2;

Re: Find specific time associated with max/min value

MarcoWedel — Sat, 06 Dec 2014 10:50:00 GMT

can you please elaborate a bit more?

thanks

regards

Marco

Re: Find specific time associated with max/min value

alkesh_sharma — Sat, 06 Dec 2014 11:13:56 GMT

Try This:

T1:
LOAD * Inline
[
Time,     High,     Low
11, 7,          1
12, 5,          2
13, 3,          1
14, 6,          3
];

tab1:
LOAD
Max(High) AS Max_High,
Min(Low) AS Min_Low
RESIDENT T1;

tab2:
MAPPING LOAD
Max_High,
1 AS Flag
RESIDENT tab1;
tab3:
MAPPING LOAD
Min_Low,
1 AS Flag
RESIDENT tab1;

DROP TABLE tab1;

tab3:
LOAD *
WHERE Max_high = 1 OR Min_Low = 1;
LOAD Time,
High,
Low,
ApplyMap('tab2',High,0) AS Max_high,
ApplyMap('tab3',Low,0) AS Min_Low
RESIDENT T1;
DROP TABLE T1;

Re: Find specific time associated with max/min value

Sat, 06 Dec 2014 14:49:26 GMT

Sure. Basically I have two tables as follows:

Table 1

Date Hour High Low

...

1/12/2014 11 7 1

1/12/2014 12 5 2

1/12/2014 13 3 1

1/12/2014 14 6 3

...

Table 2

Timezone Hour

...

London 8

London 9

London 10

London 11

NY 12

NY 13

NY 14

......

I would like to find the Hours when the highest and lowest values are for everyday during the NY timezone.