Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.
jeaninemouton
New Contributor

Nested SQL select

I am new to Qlikview and I need help to create a single table from 2 related tables with no common field. This is what I have:

Table A fields: SETNAME, VALFROM, VALTO

Table B field: KSTAR

e.g.

SETNAME    VALFROM    VALTO

Wages          1000            1004

Tea               1005            1009

KSTAR

1000

1001

1002

1003

1004

1005

.....

I need to find the SETNAME (table A) for each KSTAR (Table B) where KSTAR falls within the VALFROM, VALTO range.

Resultant table should look like this:

SETNAME

Wages 1000

Wages 1001

Wages 1002

...

Tea       1005

....

Thank you!

Jeanine

Tags (4)
1 Solution

Accepted Solutions
vijay_iitkgp
Valued Contributor

Nested SQL select

Sure...You can use same from Dbo table.

T1:

Load

*;

SQL Select

* from ABC;

T2:

Load

*;

SQL Select

* From DEF;

After this everything is same

6 Replies
vijay_iitkgp
Valued Contributor

Nested SQL select

Hi,

Please go thru this code. Hope this will help u.

T1:
LOAD * INLINE [
    SETNAME, VALFROM,VALTO
    Wages, 1000,1004
    Tea,1005,1009
];

T2:

LOAD * INLINE [
KSTAR
1000
1001
1002
1003
1004
1005];

T3:
NoConcatenate
Load
KSTAR
Resident T2;
Left Join
IntervalMatch(KSTAR)
LOad
VALFROM,
VALTO

Resident T1;
Left Join
LOad
VALFROM,
VALTO,
SETNAME
Resident T1;
Drop Table T1,T2;

Nested SQL select

Hi,

     You can try with this..

    

Table1:

LOAD * Inline

[

SETNAME,VALFROM,VALTO

Wages,1000,1004

Tea,1005,1009

];

Table2:

LOAD * Inline

[

KSTAR

1000

1001

1002

1003

1004

1005

1010

];

Left Join(Table2)

IntervalMatch(KSTAR)

LOAD

          VALFROM,

          VALTO

Resident

          Table1;

Left Join(Table2)

LOAD

          VALFROM,

          VALTO,

          SETNAME

Resident

          Table1;

DROP Table Table1;

DROP Fields VALTO, VALFROM;

Celambarasan

jeaninemouton
New Contributor

Nested SQL select

Thank you for your reply.

I don't think I made myself clear on the values of the tables. The example given is just a few values. Each of the tables have many values and can change at any time. I cannot load fixed values for these tables.

I have to load the values from the dbo tables.

Can I still use your code?

jeaninemouton
New Contributor

Re: Nested SQL select

Thank you for your reply.

I don't think I made myself clear on the values of the tables. The example given is just a few values. Each of the tables have many values and can change at any time. I cannot load fixed values for these tables.

I have to load the values from the dbo tables.

Can I still use your code?

vijay_iitkgp
Valued Contributor

Nested SQL select

Sure...You can use same from Dbo table.

T1:

Load

*;

SQL Select

* from ABC;

T2:

Load

*;

SQL Select

* From DEF;

After this everything is same

jeaninemouton
New Contributor

Re: Nested SQL select

Hi Vijay,

thank you for your answer - it works perfectly!!

I have one more question: how do I add more fields from T1 to my output file?

Even though I load 3 fields from T1, only the 2 fields used for the intervalmatch appear on the .qvd file that I store?

I also need this field: [SETNAME] as [Account Group_SETNAME].

Thank you for your help!!

Jeanine

I've attached my code.

Community Browser