topic Nested SQL select in QlikView

Nested SQL select

jeaninemouton — Mon, 06 Feb 2012 09:19:52 GMT

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

Nested SQL select

vijay_iitkgp — Mon, 06 Feb 2012 09:34:45 GMT

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

CELAMBARASAN — Mon, 06 Feb 2012 09:48:26 GMT

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

Nested SQL select

jeaninemouton — Mon, 06 Feb 2012 10:10:05 GMT

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?

Re: Nested SQL select

jeaninemouton — Mon, 06 Feb 2012 10:10:42 GMT

Thank you for your reply.

I have to load the values from the dbo tables.

Can I still use your code?

Nested SQL select

vijay_iitkgp — Mon, 06 Feb 2012 10:16:44 GMT

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

Re: Nested SQL select

jeaninemouton — Tue, 07 Feb 2012 07:29:20 GMT

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.