Mapping or left Join

[bharatkishore]

Hi All,

I have two tables. In table A  i have Equipment and Posting Date.

In table B i have  [Sales Document],
Item ,
Equipment,
Start_DT_New,
End_DT_New,
MaterialEntered

Now for [Sales Document],Item ,  and MaterialEntered the condition is i need to get only records based on the below condition i.e.

if both the table  equipments matches and Posting Date>=Start_DT_New and Posting <=End_DT_New.

Can you please tell me how to achieve this and let me know if you need anything more.

Thanks,

Bharat

[bharatkishore]

@sunny_talwar  any help Sunny Bhai..

[sunny_talwar]

Can you share a sample to show what you have?

[bharatkishore]

PFA qvw  Sunny Bhai.

Here if you filter example 10149573 equipment in chart i am getting data for Start date and end date where my posting date condition is not matching.

Here in this case the posting date is not greater than start date and less than end date.

Please let me know if you need anything more.

[sunny_talwar]

I am not sure where the join is taking place in the script, but you essentially need an IntervalMatch here just like @Vegar  mentioned in his response above. You can join the table after the intervalmatch to bring them all in a single table

[bharatkishore]

Can i add the code like the below Sunny Bhai..

left join(Revenue_Calc_2)
IntervalMatch(Equipment, [Sales Document],Item,Start_DT_New,End_DT_New,MaterialEntered)
IW75:
LOAD [Sales Document] as Sales.Document_Contract,
Item as Item_Contract,
Equipment,
Date(Date#(Start_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as Start_DT_New,
Date(Date#(End_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as End_DT_New,
MaterialEntered
FROM
[C:\Import\DT_Contract_2_for_code.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Revenue_Calc_3:
load *,

if(Posting.Date>=Start_DT_New and Posting.Date<=End_DT_New,'Contract Period','Outside Contract Period' )as Contract_Flag
Resident Revenue_Calc_2;
drop Table Revenue_Calc_2;

[sunny_talwar]

---

@bharatkishore wrote:

left join(Revenue_Calc_2)
IntervalMatch(Equipment, [Sales Document],Item,Start_DT_New,End_DT_New,MaterialEntered)
IW75:
LOAD [Sales Document] as Sales.Document_Contract,
Item as Item_Contract,
Equipment,
Date(Date#(Start_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as Start_DT_New,
Date(Date#(End_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as End_DT_New,
MaterialEntered
FROM
[C:\Import\DT_Contract_2_for_code.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

---

The highlighted part doesn't look right. Where is the Post_date in the interval match here? I think it might be easy if you can provide Excel file with one data source which contain Start_DT and End_DT and another data source which include Post_DT and add all the other necessary columns on which you are joining the two on.