Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Dear All,

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

Resident Table

CodeDateAmount
A130/04/20111000
A131/05/20112000
A130/06/20113000

Intermediate Result After Join Itself

CodeDateAmountDate1Amount1
A130/04/2011100030/04/20111000
A131/05/2011200030/04/20111000



31/05/20112000
A130/06/2011300030/04/20111000



31/05/20112000



30/06/20113000

Final Result

CodeDateAmount
A130/04/20111000
A131/05/20113000
A130/06/20116000

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

1 Solution

Accepted Solutions
Not applicable

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

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

11 Replies
whiteline
Honored Contributor II

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

Hi.

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

Not applicable

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

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

Regards

Not applicable

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

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

Not applicable

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

hi,

Please Explain properly.

Regards

Not applicable

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

TableName:

Load

     Field1,

     Field2,

     Field3,

     Field4;

SQL

( Your Query ) ;

Regards

- Anil

whiteline
Honored Contributor II

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

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

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

Not applicable

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

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

Intermediate Result After Join Itself

CodeDateAmountDate1Amount1
A130/04/2011100030/04/20111000
A131/05/2011200030/04/20111000
A131/05/2011200031/05/20112000
A130/06/2011300030/04/20111000
A130/06/2011300031/05/20112000
A130/06/2011300030/06/20113000

Final Result

CodeDateAmount1
A130/04/20111000
A131/05/20113000
A130/06/20116000

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

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

Community Browser