topic Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join in QlikView

Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 12:01:31 GMT

Dear All,

I am bit new to QV and I am stucked in wrighting data load script to achieve above final result

Resident Table

Code	Date	Amount
A1	30/04/2011	1000
A1	31/05/2011	2000
A1	30/06/2011	3000

Intermediate Result After Join Itself

Code	Date	Amount	Date1	Amount1
A1	30/04/2011	1000	30/04/2011	1000
A1	31/05/2011	2000	30/04/2011	1000
			31/05/2011	2000
A1	30/06/2011	3000	30/04/2011	1000
			31/05/2011	2000
			30/06/2011	3000

Final Result

Code	Date	Amount
A1	30/04/2011	1000
A1	31/05/2011	3000
A1	30/06/2011	6000

I can solve it by Sql Query like select * from tab1 a inner join tab1 b on a.fld1=b.fld1 and b.fld2<=a.fld1

I know in QV we can join 2 tables on a single common field directly it not possible in QV, so please provide me the wayout for it.

Thanks In Advance

Regards

- IndianQvLover

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

whiteline — Tue, 25 Sep 2012 13:18:33 GMT

Hi.

I think, you have to do it in two steps in QV, for example: join load, then load with 'where' statement.

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 13:39:21 GMT

hi, Thanks for your reply, but can you please explain more.

Regards

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 13:52:17 GMT

hi,

Directly It is not possible in QV, But as you say that you can wright query. then why you don't use it with SQL prefix

Regards

- Anil

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 13:54:17 GMT

hi,

Please Explain properly.

Regards

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 14:05:23 GMT

TableName:

Load

Field1,

Field2,

Field3,

Field4;

SQL

( Your Query ) ;

Regards

- Anil

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

whiteline — Tue, 25 Sep 2012 14:27:29 GMT

QV performs joins in a slighly different way.

On the first join 'where' statement applys to the source while you want to apply it to the result.

So you need to split your SQL select into two loads.

If you get your data from sql you can perform that query with sql statement in QV.

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

CELAMBARASAN — Tue, 25 Sep 2012 14:34:57 GMT

Simple Question is your sample table(Intermediate Result After Join Itself) is looks like the same type of data that you posted? I mean the empty values in Code, Date and Amount field?

Celambarasan

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 14:44:24 GMT

hi Celambarasan, nothing is empty in IInd table, both IInd & IIIrd look like.

Intermediate Result After Join Itself

Code	Date	Amount	Date1	Amount1
A1	30/04/2011	1000	30/04/2011	1000
A1	31/05/2011	2000	30/04/2011	1000
A1	31/05/2011	2000	31/05/2011	2000
A1	30/06/2011	3000	30/04/2011	1000
A1	30/06/2011	3000	31/05/2011	2000
A1	30/06/2011	3000	30/06/2011	3000

Final Result

Code	Date	Amount1
A1	30/04/2011	1000
A1	31/05/2011	3000
A1	30/06/2011	6000

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

CELAMBARASAN — Tue, 25 Sep 2012 14:49:58 GMT

Then you can try with this

FinalResult:

Load

Code,

FirstValue(Date) AS Date,

Sum(Amount1) AS Amount1

Resident

JoinResultTable

Group by Code;

Hope you understand

Celambarasan

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 15:08:06 GMT

hi Sir,

I am understanding what you you are writhing,

Final Result Table is not a problem, problem is Intermediate Result Table because it is deriving to join itself on 2 field and join like code=code and Date<=Date it like Sql Query

"Select * from Table1 as a inner join Table2 as b on b.Code=a.Code and b.Date<=a.Date"

Hope I explain properly

Regards

Re: Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

Tue, 25 Sep 2012 15:40:10 GMT

Hi, Anil

Sorry for late reply, I was working on your suggestion. It is working fine and I got desired final result.

Now I am working on the Mr. WhiteLine & Celambarasan Ashimulam suggestions.

Regards

IndianQvLover