left join and intervalmatch

[Anonymous]

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

[ramoncova06]

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

[Gysbert_Wassenaar · Accepted solution]

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

[Anonymous]

Thanks Gilbert! That did the job!

[sandeeps_hyd]

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:

[sandeeps_hyd]

Hi,

Thank you for the reply.

What about my case ?