topic Re: If condition in QlikView

If condition

Wed, 12 Aug 2015 08:43:12 GMT

Hello !

A made some vizualisation introduction to the subject area (attached)

From one hand, there is a table of person's tracks with time and x,y coordinates fixations ('Track' sheet in attached file) .

person_id	time	x	y
1	01:03:00	65	60
1	02:08:00	330	160

From other, there is a list of departments with their geometry presented as rectangles (x_from, x_to , y_from, y_to) ('Departments' sheet in attached file).

Dept_number	x_from	x_to	y_from	y_to
Department 1	50	130	50	90
Department 2	240	370	90	120

I am trying to develop report with the Dept_number fixation of the person in every time - with person_id, time and Dept_number columns.

person_id	time	Dept_number
1	01:03:00	Department 1

I am using the if condition in expression, but it doesn't work

if( x>=x_from and x <=x_to and y>=y_from and y<=y_to, Dept_number, 'unknown' )

Let me know if anyone have idea or decision of this problem.

I hope I was clear, thank you in advance

Ruslan

Re: If condition

kuczynska — Wed, 12 Aug 2015 09:03:50 GMT

Your tables aren't associated at all (data islands), that's why your IF statement returns only 'unknown'. You have to think about some logical way to associate your tables - did you consider using intervalmatch() function for your Departments data? Right now we have two tables that aren't related at all, you have to connect them somehow

Re: If condition

rubenmarin — Wed, 12 Aug 2015 09:39:30 GMT

Hi Ruslan, added to what Kuczynska said about unrelated tables, there are more than one record for person1 and time 01:03:00.

Resuming, I think is better to do this in script, you can use something similar to this:

[Departments geometry]:

LOAD Dept_number,

xfrom as x_from,

xto as x_to,

yfrom as y_from,

yto as y_to

FROM

[.\data.xlsx]

(ooxml, embedded labels, table is Departments);

[Persons track]:

LOAD person_id,

time,

FROM

[.\data.xlsx]

(ooxml, embedded labels, table is Track);

Link_tmp:

IntervalMatch ( x ) LOAD x_from, x_to Resident [Departments geometry];

Left Join (Link_tmp) LOAD * Resident [Departments geometry];

DROP Table [Departments geometry];

Link:

IntervalMatch (y) LOAD y_from, y_to Resident Link_tmp;

Left Join (Link) LOAD * Resident Link_tmp;

DROP table Link_tmp;

Re: If condition

Thu, 13 Aug 2015 06:17:56 GMT

Hi Kuczynska!

it 's good idea, I will go this way. Thanks for your reply

Re: If condition

Thu, 13 Aug 2015 07:09:01 GMT

Hi Ruben Marin!

Thanks for your reply, It is really helpfull.

I removed the dublicated row with 1:03:00 time.

It seems that the script ! loads many variations (actually 196 rows in 'Link' table instead of 11 fixations)

Now, I 'm thinking if it is possibe to load just one table with 3 columns by scripting : person_id, time, Dept_number

Re: If condition

rubenmarin — Thu, 13 Aug 2015 07:22:59 GMT

Hi Ruslan, after the link table is easy remove unrelated rows and let one table with 3 fields, just add this at then end of script:

Left Join ([Persons track])

LOAD Distinct x, y, Dept_number Resident Link;

DROP table Link;

DROP Fields x, y;

Re: If condition

Thu, 13 Aug 2015 11:51:50 GMT

Hi Ruben Marin !

It works greate now.Thanks again!