Nested SQL select

[jeaninemouton]

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

[vijay_iitkgp]

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;

[CELAMBARASAN]

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]

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]

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 · Accepted solution]

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]

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.