Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

aboumejjane
Contributor

left join and intervalmatch

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

1 Solution

Accepted Solutions

Re: left join and intervalmatch

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


talk is cheap, supply exceeds demand
4 Replies
ramoncova06
Valued Contributor III

Re: left join and intervalmatch

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

Re: left join and intervalmatch

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


talk is cheap, supply exceeds demand
aboumejjane
Contributor

Re: left join and intervalmatch

Thanks Gilbert! That did the job!

sandeeps_hyd
New Contributor II

Re: left join and intervalmatch

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:

IntervalMatch.PNG

Community Browser