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

[#1]

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

[whiteline]

Hi.

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

[#3]

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

Regards

[#4 · Accepted solution]

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

[#5]

hi,

Please Explain properly.

Regards

[#6]

TableName:

Load

     Field1,

     Field2,

     Field3,

     Field4;

SQL

( Your Query ) ;

Regards

- Anil

[whiteline]

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.

[CELAMBARASAN]

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

[#9]

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

[CELAMBARASAN]

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