topic Merging/Matching table with critieria in QlikView

Merging/Matching table with critieria

Mon, 12 Apr 2010 10:39:38 GMT

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

Merging/Matching table with critieria

Mon, 12 Apr 2010 11:07:52 GMT

Hi Kevin,

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

Regards,

Ravi...

Merging/Matching table with critieria

Mon, 12 Apr 2010 11:40:27 GMT

Hi,

Check the attached application.

Hope this will help you.

- Sridhar

Re. :Re: Merging/Matching table with critieria

Mon, 12 Apr 2010 12:11:34 GMT

Thanks guys,

Yes Ravi, I think that could work.

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

Merging/Matching table with critieria

Mon, 12 Apr 2010 12:15:09 GMT

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

Re. :Re: Merging/Matching table with critieria

Mon, 12 Apr 2010 13:06:09 GMT

Thanks thanks,

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

Kévin

Re. :Re: Merging/Matching table with critieria

Mon, 12 Apr 2010 14:58:26 GMT

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;

Re. :Re: Merging/Matching table with critieria

Tue, 13 Apr 2010 10:12:58 GMT

Hi kevin,

Good solution.

Regards,

Ravi....