Help with intervalmatch

[#1]

Hi

I have tryed  so many combinations now but do´t get my intervalmatch to work.

My goal is to show what household a person belongs to when a transaction occurs and what amout the transaction are. + Sums for all transaction under a certain timeperiod. The link to a calender are maid later on by a datekey.

My problem are mainly that the amount, sums are messed up after the intervalmatch.

I have 3 tables, maybe we only need to use 2 of them...

Table 1: Household. Start & StopDate for evry household and unic household ID and SocialsecurityID. A person can over time belong to diffrent household but should newer be in more then one household at the time. The problem are also that sometimes a transaction occurs to a person that don´t belong to a household. In that case the amout/transaction should still be valid but I don´t know hove to solve this.

(I tryed to concatenate this after the intervalmatch by where not exists)

Table 2: Transactions. TransactionDate and amount of money and SocialsecurityID

Table 3: Class. Class-start & StopDate. ClassID and Socialsecuritynumber. No transactions occurs without a valid class.

Not sure if I have to use this table but my thought were that to use this in a first intervalmatch against the househould start-stopp and then in a second intervalmatch use transactiondate against househould start-stopp. No when I look in the scrtptingmirror I´m not really sure about this... Been a to long night with not much sleep becuase of a wonderful but not so sleepy baby.. 🙂

Yes. the Socialsecuritynumber have been autonr i the same step so the mach.

I´m not sure if I should do has I have tryed and generate all possible dates by this or only use the dates in the tables.

LET StartDate= num(date#('2010-01-01', 'YYYY-MM-DD'));
LET EndDate= num(addyears(yearend(today()),1));

TempDate:
LOAD
Date(($(StartDate)+Iterno()) -1) as Temp_Date
Autogenerate 1
While Date($(StartDate)+(Iterno()-1)) <=Date($(EndDate));

and then use Temp_Date in the intervallmatches.
 

[#2]

Iterno() use whith WHILE?

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#4]

Thanks! Is it poosible to do a intervalmatch between the Household start-stop, SocialsecutiryNR and every date
and then in the script check if any person belongs to more then one household at time but the autigenerate function?

LET StartDate= num(date#('2010-01-01', 'YYYY-MM-DD'));
LET EndDate= num(addyears(yearend(today()),1));

TempDate:
LOAD
Date(($(StartDate)+Iterno()) -1) as Temp_Date
Autogenerate 1
While Date($(StartDate)+(Iterno()-1)) <=Date($(EndDate));

and then use Temp_Date in the intervallmatches.