topic left join and intervalmatch in QlikView

left join and intervalmatch

Anonymous — Wed, 17 Jun 2015 14:25:49 GMT

Hi guys,

Please look at the below script. When I load this script it works just fine.

The momement I add a field, it load the script, but it Qlikview complains that it cannot find any of the fields in my facts table. The moment I remove the extra field it works great!

What am i doing wrong?

I tried to add the field in the first load (green color). later I tried to add the field in the preceding load. That didn't work.

Left Join (TMP2_Facts)

IntervalMatch(orderdate.KEY, OrderCompliant.KEY)

Load

numStartContract,

numEndContract,

OrderCompliant.KEY

contractCompliant.KEY, TEST2

OrderCompliant.KEY as orderContractcompliant //TEST1 did not work

;

Load

contractCompliant.KEY AS OrderCompliant.KEY,

contractCompliant.KEY, //TEST 1 did not work

Min(Num(contractBegindatum)) AS numStartContract,

Max(contractEinddatum_Temp) AS numEindeContract

Resident TMP_Contract

Group By

contractCompliant.KEY;

Drop Table TMP_Facts;

Hope someone can explain.

Cheers

Sam

Re: left join and intervalmatch

ramoncova06 — Wed, 17 Jun 2015 14:30:06 GMT

you need to put your key fields inside of a () in the intervalmatch

Re: left join and intervalmatch

Gysbert_Wassenaar — Wed, 17 Jun 2015 14:32:59 GMT

If your intervals are defined per combination of fields including the extra fields then you need to add those to the intervalmatch too:

Left Join (TMP2_Facts)

IntervalMatch(orderdate.KEY, OrderCompliant.KEY,contractCompliant.KEY, TEST2)

Load

numStartContract,

numEndContract,

OrderCompliant.KEY

contractCompliant.KEY, TEST2

Re: left join and intervalmatch

Anonymous — Wed, 17 Jun 2015 15:41:09 GMT

Thanks Gilbert! That did the job!

Re: left join and intervalmatch

sandeeps_hyd — Fri, 18 Jan 2019 10:44:29 GMT

Hi,

Can you please let know why the below script shown in youtube video is not working.

Sales:
LOAD salesperson_id,
sales_date,
sales_amt
FROM
Sales.xlsx
(ooxml, embedded labels, table is Sales);

Offices:
LOAD salesperson_id,
salesperson,
office,
startdate,
enddate
FROM
Sales.xlsx
(ooxml, embedded labels, table is Offices);

Inner Join(Offices)

IntervalMatch('sales_date')
LOAD
startdate,
enddate
Resident Offices;

Output:

Re: left join and intervalmatch

sandeeps_hyd — Tue, 05 Mar 2019 06:49:45 GMT

Hi,

Thank you for the reply.

What about my case ?