Merging/Matching table with critieria

[#1]

Hey everybody,

I've got this situation, 2 tables which I've to merge/match.

The first one with a status and a value :

Contract:
LOAD *
INLINE [
Status,Value
'Signed',60
'Pipe',90
'StoF',100
];

The second one with a week and an objective value

Week:
LOAD *
INLINE [
Week, Objective_value
S1, 10
S2, 20
S3, 30
S4, 40
S5, 50
S6, 60
S7, 70
S8, 80
S9, 90
S10, 100
];

What I want is for each week, the status of the week : signed/pipe/StoF

Table:
LOAD *
INLINE [
Week, Status,Objective_value
S1,'Signed', 10
S2,'Signed',20
S3,'Signed', 30
S4,'Signed', 40
S5,'Signed', 50
S6,'Signed', 60
S7,'Pipe', 70
S8,'Pipe', 80
S9,'Pipe', 90
S10,'StoF', 100
];

The match criteria is : Objective_value (Week) <= Value (Contract)

If anybody has a idea how I can get from from the 2 first tables to the last one

[#2]

Hi Kevin,

As per my understanding, this can be achive using intervalmatch. Just try..

Regards,

Ravi...

[#3]

Hi,

Check the attached application.

Hope this will help you.

- Sridhar

[#4]

Thanks guys,

Yes Ravi, I think that could work.

Sridhar, I'm using QV Personnal so I can't open ur file

[#5]

ok, then find below code for you query and find the o/p as image.

Contract:

LOAD *
INLINE [
Status,Value
'Signed',60
'Pipe',90
'StoF',100
];

join
Load Week,Objective_value as Value;
LOAD *
INLINE [
Week, Objective_value
S1, 10
S2, 20
S3, 30
S4, 40
S5, 50
S6, 60
S7, 70
S8, 80
S9, 90
S10, 100
];

noconcatenate

Contract1:
Load * Resident Contract order by Value Desc;

Drop table Contract;

Load *, if(len(Status)<>0,Status,Peek(Status_New)) as Status_New Resident Contract1;

Drop table Contract1;

- sridhar

[#6]

Thanks thanks,

In my case, the 1st solution will work better. But both solutions are quiet great.

Kévin

[#7]

That's my solution with IntervalMatch :

Contract:
LOAD *
INLINE [
Status,Value1,Value2
'Signed',0,60
'Pipe',61,90
'StoF',91,100
];

Week_objective:
LOAD Week, Objective_value
INLINE [
Week, Objective_value
S1, 10
S2, 20
S3, 30
S4, 40
S5, 50
S6, 60
S7, 70
S8, 80
S9, 90
S10, 100
];
inner join IntervalMatch(Objective_value) load Value1,Value2 resident Contract;
inner join Load * resident Contract;

//removing the fields Value1 and Value2
Table:
Load Week,Status,Objective_value
resident Week_objective;

drop table Week_objective;

drop table Contract;

[#8]

Hi kevin,

Good solution.

Regards,

Ravi....