Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi.
I think, you have to do it in two steps in QV, for example: join load, then load with 'where' statement.
hi, Thanks for your reply, but can you please explain more.
Regards
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
hi,
Please Explain properly.
Regards
TableName:
Load
Field1,
Field2,
Field3,
Field4;
SQL
( Your Query ) ;
Regards
- Anil
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.
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
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 |
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