topic Re: Interval Match error in QlikView

Interval Match error

rcandeo — Wed, 11 Dec 2013 16:37:24 GMT

Anyone can explain me why this interval match is not working? Lines 4,5,6 and 13, 14 and 15 shouldn´t exist since the date/time are not between the interval.

Re: Interval Match error

hic — Wed, 11 Dec 2013 17:30:14 GMT

Because you have missed to use the field Equipment as parameter to the IntervalMatch prefix.

inner join (Worker)

IntervalMatch(Date_Time, Equipment) LOAD DateTime_Initial,DateTime_Final,Equipment RESIDENT Worker;

HIC

Re: Interval Match error

rcandeo — Wed, 11 Dec 2013 18:01:51 GMT

After using this field I have the double of the lines and I can see this if I create a dinamic table with Name field using count(Name) expression.

Select Name = A and Equipment = Y and I the result is 6, when I should have only 3 lines.

Can you explain me why?

Re: Interval Match error

hic — Thu, 12 Dec 2013 09:04:04 GMT

Yes, and that is the correct result of the Join. The Join produces one record per combination of Worker and Date_Time.

You should perhaps instead join the intervalmatch table onto the Production table?

Or - the solution that I prefer - you should not join at all:

TempTable:

IntervalMatch(Date_Time, TempEquipment)

LOAD DateTime_Initial,DateTime_Final,TempEquipment RESIDENT Worker;

BridgeTable:

Load TempEquipment & '|' & Date_Time as Key1,

TempEquipment & '|' & DateTime_Initial & '|' & DateTime_Final as Key2

Resident TempTable;

Drop Table TempTable;

Drop Field TempEquipment;

You need to create the Keys and the TempEquipment fields in the two initial tables, and you need to remove the Equipment field from one of the tables.

HIC

Re: Interval Match error

rcandeo — Thu, 12 Dec 2013 15:49:30 GMT

sorry, but I´m missing some point cause I cannot be able to fix this by myself.

Re: Re: Interval Match error

hic — Thu, 12 Dec 2013 17:50:34 GMT

Attached you find my version of the file.

HIC

Re: Interval Match error

bdunphy — Wed, 18 Dec 2013 17:09:41 GMT

Hi Henric.

If I had multiple rows in my lookup table (Worker in your example) and I wanted to use Generic Load after the interval match to place those rows as columns into the Production table what would I need to do ? or would this even be possible ?

In my example - I have timestamps for samples in a samples table and a 2nd table created with extracts from a machine log. These are taken at 15 minute intervals so I am using interval match to bring in the machine state for 13 different settings for any sample time.

I would now like to add those as columns to my original samples table to allow easier tabular reporting first but I also have a number of charts built around the expressions as a variable and adding the 'new' expressions from my machine table to the samples table would allow me to extend these.

Apologies if this should be a new thread - just seems to fit the sample file given

Regards,

Brian

Re: Interval Match error

hic — Wed, 18 Dec 2013 19:14:40 GMT

Difficult to say without having seen the data, but in prinicple there is nothing that prevents you from using a combination of intervalmatch and generic. You just have to do it in two or more steps since you cannot have both prefixes on the same load.

HIC